Kategorijos

How Small Businesses Automate Routine Reporting With Scripts

A weekly report is often a short ritual, not a missing dashboard. On the r/excel forum, one manager described a shared workbook with a pivot table of open items. Every week the same person refreshed the pivot, saved a copy to a SharePoint folder, and emailed it to a manager. The email step was already on a schedule. The refresh and the save still were not.

That pattern is common in small firms: the numbers already live in a tracker, a CRM export, or an advertising cabinet. A person still copies them into a pack that goes out on Monday.

Spreadsheets are not disappearing. In May 2024 the Institute of Chartered Accountants in England and Wales issued the fourth edition of its Twenty principles for good spreadsheet practice, first published in 2013 and previously updated in 2018. Ian Pay, ICAEW’s Head of Data Analytics and Tech, pointed to new Excel functions, competition from tools such as Google Sheets, and more collaborative work. The new edition, he said, focuses on working with growing volumes of data. Following the principles will not solve every spreadsheet error, but it might reduce the risk.

The weak point is often the assembly, not the workbook itself. ICAEW principle 4 asks whether a spreadsheet is a suitable tool at all. Principle 8 says to design with adaptability in mind, to minimise future manual intervention. Principles 17 to 19 call for checks and alerts from the outset, a test-and-review step, and a regular process of backup and version control, so last week’s file is not the one that gets sent.

Older field evidence shows why those controls exist. On his spreadsheet-research site, Ray Panko summarises five operational audits that used stronger methods and counted only important errors. Together they examined 55 workbooks, from studies published between 1995 and 2000. The audits found important errors in 91 percent of them. The sample is small and dated. It is still a warning against treating a hand-built weekly pack as automatically trustworthy.

A later picture of reporting work comes from finance teams, rather than from small-firm Monday packs alone. In a 24 May 2019 article, APQC described a research project with the Association for Financial Professionals. Survey participants, APQC wrote, reported spending 75 percent of their time gathering data and administering processes, only two percentage points lower than when APQC had asked the same question in 2010.

A more recent vendor survey should be read with its sample in view. Intuit QuickBooks commissioned an online survey in August 2024 of 630 owners and executives at U.S. firms with 10 to 99 employees. Respondents said their businesses spent, on average, 25 hours a week on manual data entry or reconciling data across apps. Fifty-four percent named manual and repetitive tasks as a challenge with their current digital tools; 45 percent named inadequate reporting and analysis.

The first report worth scripting is the one that already has a fixed rhythm: the same sources, the same steps, the same output, every week. The r/excel example qualifies. A quarterly pack that depends on judgment does not.

What should the first script actually do?

One practical sequence is described at sverdlov.pro. The firm is based in Moscow and is led by its founder, Alexander Sverdlov. On its page about automating Google Sheets and Excel, it sets out a four-step chain for a recurring report that still lives in tables. A script collects data from systems such as a CRM, an accounting tool, advertising cabinets, or a bank. It calculates the agreed figures. It writes a summary sheet, including a comparison with the previous period. It then sends a link and the main figures to a manager by email or Telegram.

Google Apps Script can do this inside a Google Sheet, within Google’s daily quotas; the firm says those quotas are usually enough for a small business. For Excel files that sit on a local computer, the same page notes that automation is harder, which is why a report is often moved into Google Sheets. When volume, quotas, or logic outgrow Apps Script, the work is written as a Python script. That script can run on an employee’s computer at the push of a button, or on a server on a schedule. It should write a log and send an alert if a source changes its export format, so a broken feed is noticed the same day rather than at the end of the month.

Prices for table automation and for Python scripts are given on request, after a look at the actual files or a sample of the data. Code and instructions stay with the client. A process audit is described as usually taking about three to five days. A pilot on one process usually lasts two to four weeks, with the old path kept available while the team compares figures before and after.

A script will not repair a messy source. ICAEW principle 10 is to control, clean, and optimise data quality and structure before the data are used. If a column mixes notes and numbers, or if “done” has no written definition, write the rule and tidy the file first. Keep a person reviewing anything that goes to a manager or moves money.

Tables remain a reasonable first home for the report when there are one or two organisations, the categories are clear, and only a few people read the file. The same site says it is time to consider a dashboard rather than another sheet when the file becomes slow, many people need different access rights, or there are more than five sources that update often. That is a later step, not the first script.

What to do this week

  • Write down one report that already goes out every week: who receives it, which systems it draws on, and what “done” looks like.
  • Time one cycle: how many minutes it takes, how many files are opened, and where a figure is retyped or a pivot is refreshed by hand.
  • Write the rule for each figure in one sentence, including the date field and what is included.
  • Cross out reports that happen rarely, depend on judgment, or cannot be undone.
  • Tidy the source file if columns are inconsistent, then script only the remaining, rule-based cycle.
  • Keep the old path available for one or two cycles, and agree who will check the output before it is sent.
  • Add an alert if a source fails, so silence is not mistaken for a successful run.

The aim of the first script is not a new reporting platform. It is to take one report that already has a weekly ritual and remove the copy, the save, and the risk of sending last week’s file.

Naujausi straipsniai

reikia hostingo?

Comments

Parašykite komentarą

El. pašto adresas nebus skelbiamas. Būtini laukeliai pažymėti *

This site uses Akismet to reduce spam. Learn how your comment data is processed.