Shobhit Institute Students: Automating Repetitive Reports with Excel and Python in an Internship

You can automate a repetitive report in two weeks if you pick one stable, frequently repeated task, rebuild it as a script that reads a raw file and writes a finished workbook, and keep every original file untouched. Excel handles the layout and quick checks, Python handles the repeated cleaning and combining, and a before-and-after worksheet proves what you saved. This guide gives you that worksheet, a starter script outline, a backup safety checklist and a 14-day plan built for CareerSwami’s free two-week online internship or any similar short project.

Which reporting tasks are actually worth automating

Not every report deserves a script. A task that takes 20 minutes once a quarter will cost you more to automate than to repeat. A task that takes 30 minutes every week, follows the same steps and breaks only when someone changes a column name is an ideal candidate.

Score each candidate task on five questions:

• Frequency: how often is it done? Daily and weekly tasks pay back fastest.
• Stability: do the input files keep the same columns, sheet names and layout each time?
• Rule-based steps: can you write the steps as plain instructions (filter, remove duplicates, group, sum, paste into a template) without human judgement at each step?
• Error cost: do manual copy-paste mistakes happen? Automation removes slips, but only if the logic is right.
• Size: is the data large enough that Excel is slow, or are there many files to combine?

Tasks that usually fail the test: reports whose layout changes every month, anything needing subjective review of each row, and one-off analysis where the question itself keeps changing.

Before-and-after automation worksheet for report tasks

Fill in one row per candidate task before you write any code. Record the “before” numbers by timing yourself doing the task manually at least twice and taking the average. The “after” columns are filled in once the script works.

The break-even column keeps you honest. If a task breaks even after 40 runs, it is not an internship-sized project. Pick another.

A worked example: weekly branch sales summary (illustrative)

The numbers below are invented to show the method. They are not from any real organisation, and no script has been run to produce them.

The manual process: Each Monday, five branch CSV files arrive by email. The analyst opens each one, deletes the header junk in the first three rows, copies the data into a master sheet, removes duplicate invoice numbers, adds a Branch column, builds a pivot table of sales by product category, formats it, and pastes it into a report template. Timed twice, it takes 47 and 43 minutes: an average of 45.

What to automate: Everything up to and including the pivot summary. The final commentary paragraph stays manual because it needs judgement.

What the automated run does:

1. Copies the five input files into a dated backup folder.
2. Reads each CSV, skips the three junk rows, and adds the branch name taken from the file name.
3. Stacks all five into one table and drops duplicate invoice numbers.
4. Runs validation checks: required columns present, no negative quantities, row count equals the sum of the individual files minus duplicates.
5. Builds the category summary and writes it to a new dated Excel workbook, with the cleaned data on a second sheet.
6. Prints a short log: files read, rows in, duplicates removed, rows out.

Result in the worksheet: after-run time is 8 minutes (drop files in, run, read the log, add commentary). Saving is 37 minutes per run, 148 per month, with break-even after about ten runs if the build took six hours. In a two-week internship you will not reach ten live runs, so state that clearly: report the projected saving, label it as a projection, and show the evidence you do have, such as test runs on sample files.

Where Excel is enough and where Python earns its place

Start in Excel. Many “automation” wins need no code at all, and knowing the boundary is a skill recruiters value.

• Use Excel tables, structured references and named ranges so formulas expand when data grows.
• Use Power Query (Get & Transform) to import, clean and append files from a folder. It records steps and replays them on refresh.
• Use PivotTables for summaries that fit a standard layout, and refresh them instead of rebuilding.
• Use Python when you need to loop over many files, apply validation rules you can test, handle awkward formats, produce several outputs at once, or keep the logic in a readable, version-controlled script.

A good internship deliverable often uses both: Python prepares and validates the data, then writes an Excel file with formatted sheets that non-programmers can open and read.

Starter script outline for a Python report automation

This is an illustrative outline, not tested code. Treat it as a skeleton to adapt: you must run it on your own sample files and fix column names and paths. It assumes Python with pandas and openpyxl installed.

from pathlib import Path
from datetime import date
import shutil
import pandas as pd

INPUT_DIR = Path("input")        # raw files; never edited by the script
BACKUP_DIR = Path("backup") / str(date.today())
OUTPUT_DIR = Path("output")
REQUIRED = ["invoice_no", "category", "quantity", "amount"]

def backup_inputs():
    BACKUP_DIR.mkdir(parents=True, exist_ok=True)
    for f in INPUT_DIR.glob("*.csv"):
        shutil.copy2(f, BACKUP_DIR / f.name)

def load_one(path):
    df = pd.read_csv(path, skiprows=3)
    df["branch"] = path.stem
    return df

def validate(df):
    missing = [c for c in REQUIRED if c not in df.columns]
    if missing:
        raise ValueError(f"Missing columns: {missing}")
    if (df["quantity"] < 0).any():
        raise ValueError("Negative quantities found")

def build_summary(df):
    return (df.groupby(["branch", "category"], as_index=False)
              .agg(units=("quantity", "sum"), sales=("amount", "sum")))

def main():
    backup_inputs()
    frames = [load_one(p) for p in sorted(INPUT_DIR.glob("*.csv"))]
    rows_in = sum(len(f) for f in frames)
    data = pd.concat(frames, ignore_index=True)
    validate(data)
    data = data.drop_duplicates(subset="invoice_no")
    summary = build_summary(data)
    OUTPUT_DIR.mkdir(exist_ok=True)
    out = OUTPUT_DIR / f"weekly_summary_{date.today()}.xlsx"
    with pd.ExcelWriter(out) as xl:
        summary.to_excel(xl, sheet_name="Summary", index=False)
        data.to_excel(xl, sheet_name="Clean data", index=False)
    print(f"Rows in: {rows_in}, rows out: {len(data)}, file: {out}")

if __name__ == "__main__":
    main()

How to extend the outline after it works

• Move paths and column names into a small settings block or config file.
• Add a reconciliation check: total sales in the summary must equal total sales in the cleaned data.
• Write a log file rather than only printing to the screen.
• Add a short README describing inputs, outputs, how to run, and known limits.
• Test with a deliberately broken file (missing column, text in a number field) to confirm the script stops with a clear message instead of producing a wrong report.

Backup and safety checklist before you run any automation

Automation makes mistakes fast and at scale. Work through this list before the first run and again before each handover.

1. Keep raw inputs read-only. The script reads from an input folder and writes only to an output folder. Never save over a source file.
2. Back up first, automatically. The script copies inputs to a dated folder before doing anything else.
3. Follow the 3-2-1 habit where you can: three copies, on two kinds of storage, one away from your laptop, such as a cloud drive you are permitted to use.
4. Use sample or anonymised data while developing. Remove names, phone numbers, emails and ID numbers. Do not move real organisational data to a personal device unless you have written permission.
5. Version your outputs. Put the date in file names so a rerun never overwrites yesterday’s report.
6. Save scripts with version history. Use Git or simply numbered copies (v1, v2) with a one-line note of what changed.
7. Make the script fail loudly. Missing columns, empty files and impossible values should stop the run with a message.
8. Reconcile before sharing. Compare row counts and totals between input and output. Spot-check at least five rows by hand.
9. Test a restore. Once, delete a copy and recover it from the backup so you know the backup works.
10. Keep secrets out of code. No passwords, API keys or personal paths in files you submit or publish.
11. Disable risky Excel macros from unknown sources and avoid running scripts you did not write or review.

A 14-day plan for an automation project inside a two-week internship

Fourteen days is short, so scope one report, not a whole department. Adjust the days to your own availability.

Shobhit Institute of Engineering & Technology: Training Support Behind Excel and Python Report Automation

Shobhit Institute of Engineering & Technology, Meerut, operates as a private deemed-to-be university at NH-58, Modipuram. Its official website lists B.Tech, BCA and MCA programmes, including a BCA in Data Science and an MCA in Data Science & Machine Learning, so report-automation skills with Excel and Python sit close to what many students there already study.

Career services run through the University Training Development Center. According to its placements overview, the centre offers individual career assessments, guidance sessions and personal profiling, and it arranges internships with the same range of organisations that recruit from the university. It also runs internal training workshops, mentorship and skill-development modules, which is a sensible place to ask about data-skills sessions before you start an internship.

The UTDC Office page adds that the office helps place students for training with corporate organisations in order to fulfil curriculum requirements. For a student automating repetitive reports, this matters in a practical way: if the placement counts toward your course, your host organisation and mentor details are likely to be recorded, so the automation project you describe should match what is in your approved internship paperwork.

The site also carries an internship and placement calendar page. An older schedule has been shown there, so treat any dates you see as a reference to verify, not a current timetable. The pages read do not publish duration, credit or evaluation rules, so those must come from your department.

Verification steps for report-automation interns

• Ask the UTDC Office whether your internship counts toward a curriculum requirement and which semester it belongs to.
• Request the current internship and placement calendar and compare it with the version on the website.
• Check what documents the university needs from the host organisation, such as an offer letter or completion certificate.
• Confirm with your mentor which company data you may use in scripts and what must stay on company systems.
• Ask your department how the project will be assessed and whether code or a written report is expected.

How to present the automation result without overstating it

A reviewer cares about three things: did you choose a sensible problem, can you prove the output is correct, and can someone else run it? Present your result in that order.

• State the baseline honestly: “Manual process timed twice: 43 and 47 minutes” is better than “took about an hour”.
• Separate measured from projected: if you ran the script on three sample files, say so, and label monthly savings as projections.
• Show the checks: a short table of row counts and total reconciliations is more convincing than a screenshot of a pretty chart.
• Name the limits: for example, “works only if column names stay the same; fails with a clear message if not”.
• Write for the next person: a README that lets a colleague run the process in five steps is itself a deliverable.

Frequently Asked Questions

How do I decide whether to use Excel Power Query or Python for a report?

Use Power Query when the data sources are simple, the steps are mostly import, filter, merge and pivot, and the people who will maintain it work in Excel. Choose Python when you need custom validation, many files, repeatable tests or logic that is easier to read as code. Many projects combine both.

How many hours should I expect a first automation to take?

For a single report with a handful of input files, a realistic build is several hours spread over a few days, including testing. Write down your estimate in the worksheet, then record the actual time. The gap teaches you to scope better next time.

What is the minimum backup routine for a student automation project?

Keep raw inputs untouched, copy them to a dated backup folder at the start of every run, version your script, and date your output files. Test restoring one backup at least once. These four habits cover most accidental data loss.

Can I use real company data in my project?

Only if you have written permission and the data is anonymised where it contains personal details. If you are unsure, build the project with a sample or synthetic dataset that mimics the structure. The skill you are demonstrating is the process, not the data.

How do I prove that my automated report is correct?

Reconcile it with the manual version on the same inputs. Compare row counts, grand totals and a few category totals, then hand-check a sample of individual rows. Include a short table of these comparisons in your project summary.

What should I do if the input file layout changes after I finish the script?

Your validation step should catch it and stop with a clear error naming the missing column. Then update the settings block that holds column names and rerun your tests. Document this in the README so the next person knows where to look.

Is a 14-day online internship enough time to show real automation skills?

Yes, if you limit the scope to one report and document it well. A small, correct, well-explained automation with honest time measurements is stronger evidence than a large unfinished one. The free two-week internship from CareerSwami fits this kind of focused project.

Will a Shobhit department accept this project for internship requirements?

That depends on your programme’s current rules, which should be confirmed in writing with your coordinator or training and placement office. Ask about accepted duration, mode, report format and signatures before you start, and keep the reply with your project files.

Related Data Analytics guides

• Jaipur National University Students: Writing a One-Page Data Analytics Project Proposal
• Bhattadev University Students: Collecting and Cleaning Your Own Data for an Analytics Internship
• UET Roorkee Students: Using Git and Version Control During a Data Analytics Internship
• All Data Analytics internship guides