Paper Logs to Monthly Reports: A Substation Lesson

A handwritten record usually changes hands several times before it becomes a monthly report: a value is read, written on paper and entered into a system, and at the end of the month someone else reads those sheets again and types them into Excel. Errors are mostly born not in the measurement but in these handovers. The way to shorten the chain is first to simplify what the report really needs, then to record the value once, legibly, at the place where it is first read. The collecting can be left to software; the assessment and the decision stay with people.

This article is written from the point of view of someone who has worked in high-voltage substation operation for 11 years and has been developing software for businesses for 5 years. In the substation, reading the values every hour on the hour and entering them into the system by hand, and at the end of the month building the monthly MAX-MIN table in Excel from the other technicians' daily handwritten logs, is an ordinary part of the job. Below there are no details about the facility itself; only a work pattern that you may meet in a small business as well.

Hourly readings, handwriting and month end: the journey of a report

In a substation, the road to the report starts like this: every hour on the hour, the measured values are read and noted down. The same values are also entered by hand into the record system on the computer. Over the day, different technicians do this work; each writes in their own handwriting and in their own way. When the month ends, someone sits down, takes all the daily records of that month and works out in Excel the highest and lowest point of each value within the month, in other words the MAX-MIN table.

Counting this journey step by step makes the picture clear. A value is first read by one pair of eyes, written on paper by one hand and typed into the system from one keyboard. At the end of the month another pair of eyes reads that paper again and another hand types it into Excel. The same number has been written at least three times and read twice. Every writing and every reading is a moment when the value may change slightly.

This chain is not unique to substations. A shop that writes the end-of-day cash count in a notebook, a building management that copies meter readings onto a form or a workshop that writes production quantities on a board may follow the same road: first paper, then a spreadsheet, at the end of the month a report.

A distinction needs to be made clear here. The article Why Am I Entering the Same Thing Twice? describes the same information being entered side by side into two separate programs. The subject of this article is the chain a value passes through from paper to spreadsheet, from the moment it is read until it reaches the monthly report, and how that chain can break at each link.

Where errors arise when transferring from paper to Excel

A transfer error usually comes not from carelessness but from the paper itself. The person preparing the table at the end of the month cannot ask the person who wrote the record a question at that moment; the only source in their hands is the paper. These are the places where errors most often arise.

Hard-to-read handwriting

In a hastily written figure, one and seven, zero and six, four and nine can be mixed up. For the writer the record is clear, because they know what they wrote. The person reading it at the end of the month has to guess. Once a guessed figure enters the table, it looks like a correct value.

Shifted rows and columns

On a long daily form the eye can slip one row down. The value of one hour can be written into the neighbouring hour's cell, the value of one measurement into the column of the measurement next to it. The figure was read correctly but put in the wrong place. This error is not noticed at first glance, because the value looks reasonable.

Decimal mark and unit

On paper a comma, a point and sometimes an ink smudge look alike. One person may have written a value in one unit and another person in a different unit. Excel cannot know this difference; it accepts whatever is typed as a number and may pick the highest or lowest value from that faulty number.

The empty hour

At a busy moment one hour's reading may be skipped or left empty to be added later. At the end of the month the person facing the empty cell has to decide whether to leave it empty, estimate it from the neighbouring values or ask the person who wrote the record. Each option can pull the report in a different direction.

Why the MAX-MIN table takes so long once a month

At first glance the MAX-MIN table is a simple job: the highest and lowest point of each value within the month is found. In Excel this is done with a formula. The time-consuming part is not the formula but rebuilding from paper the table the formula can work on. There are several reasons for this:

• The work piles up at month end: daily records wait on paper all month and the transfer is done in one sitting. When a whole month of records comes before you at once, fatigue can bring errors with it.
• Every page is a different handwriting: the reader has to get used to each writer's habits again on every page.
• Uncertainty is cleared up only by asking: for an unreadable figure the person who wrote it has to be asked; that person may not be at work that day.
• The value has already been entered once: in the substation example the hourly values are already entered into the system by hand, yet the monthly table is still built from the paper records. The same work is done twice, and when the two records do not match, which one is correct has to be investigated separately.
• Checking is also done by hand: to see whether the transferred table matches the paper, the same pages have to be looked at once more. If the check is skipped, the error goes into the report; if it is done, the work gets longer.

In the end the report itself is a short table, but reaching it requires rewriting all the records of the month. Because the report is prepared once a month, this load lands in front of the same person every month.

Simplify first: which values really go into the report

Before moving the chain into software, the question to ask is this: who is this report for, and which decision is it prepared for? A paper routine grows over the years; a column that was once needed may go on being filled in every day even though nobody looks at it any more. Moving a messy routine into software as it is carries the unnecessary steps along too.

These questions help with simplifying:

• Who reads the report: does it go to a manager, or does it wait in a folder? A column without a reader can be taken out of the collecting load.
• Which decision rests on this report: a maintenance plan, a capacity assessment, an invoice check? Once the decision is clear, the values needed become clear as well.
• Which value goes into the report and which stays in the record: every hourly reading can stay in the record; the report often needs only the highest and lowest value and the time they were seen.
• Time is a value too: the day and hour when the highest value was seen carries as much information as the value itself. This is the information most easily lost when transferring from paper.
• In how many places do you write the same value: if paper, the system and Excel hold the same number, it is decided from the start which of them will be the master record.

This step does not need software. A sheet of paper and a short conversation with the person who uses the report may be enough to decide what will be recorded. The resulting list draws the frame of the next step, namely how the record will be kept.

Writing the reading once: recording at the source

The weakest link in the chain is the same value being written more than once. Recording at the source means the value is written once, legibly and in the right field, at the place where it is first read. Everyone after that reads that record; nobody writes it again.

This may not need a large system. What matters is that the record carries a few rules:

• Fixed fields: every value has its own named field. Because the value is written into that field rather than onto a row, shifting rows becomes harder.
• Automatic date and time: the time of the record is not written by hand; it is filled in at the moment of recording.
• Fixed unit: the unit is written next to the field; the person recording enters only the number.
• Reasonable range warning: if the value is outside the range expected for that measurement, a warning appears during recording. The warning does not stop the record; it asks the writer whether the value was read correctly.
• The name of the writer: if a value raises a question at the end of the month, it is clear whom to ask.
• The reason for an empty field: if a reading could not be taken, the field is not silently left empty but marked with a short note.

If the measuring device can supply the data itself, the reading can be taken away from people; this is not possible everywhere and does not need to be the first step. Paper has its place too: when power or the connection is lost, a paper form can be kept as a backup. The difference is this: paper is no longer the master record but a backup, and it is entered into the system once when the connection returns.

Excel itself is not the opposite of this approach. A shared table with locked fields and validation rules is an important step forward from paper. The criteria in Customer Tracking in Excel: When Is It Not Enough?, which uses a customer list to show when Excel stops being enough, can also guide a record table.

Collecting the report automatically, leaving the decision with people

Once values are recorded at the source in orderly fields, the monthly MAX-MIN table stops being a rewriting job and turns into a query. From the records accumulated over the month, the highest and lowest point of each value, together with the time it occurred, can be collected by software. The report does not have to wait for the end of the month either; on any day of the month the table up to that day can be seen.

The line has to be drawn in the right place here. Software collects, sorts and shows the value that stands out; people make the assessment and the decision. When a value goes outside what is expected, it is the person who knows the field who tells whether it is a reading error, a device problem or a real change in load. The decision on the switching operation carried out in a substation during a fault to keep the outage short is the same: records and screens help, the decision belongs to the person on site. In a small business it is no different; a report may show a warning, but what to do about that warning is decided by the person who knows the work.

The same logic can be set up for other reports. For example, tracking the reactive charge on an electricity bill every period is also a chain of values; the article How Is a Reactive Power Penalty Tracked? explains why manual tracking is hard to keep up and what a period report contains.

A checklist for finding the same chain in your own business

You can ask the questions below for any monthly report in your business: cash, stock, production, meters or sales. The more "yes" answers there are, the longer the chain, and it may be worth shortening.

A few "yes" answers on this list do not mean you have to move to software right away. The first step is often the simplification above. Once the place and form of the record are clear, how the collecting can be automated can be discussed.

Frequently Asked Questions