How to Build a Spreadsheet Research Log You Can Audit Later

A research spreadsheet should help you trace a conclusion back to the observation that supports it. Start with that relationship before choosing colors, dashboards, or charts. If the workbook cannot distinguish a source value from your interpretation, a polished summary may conceal uncertainty rather than organize it. The simplest useful design gives each observation a stable identity and preserves the context needed to understand it later.

This workbook design is an original research framework, not a validated investment strategy. The SEC's API documentation describes filing metadata and structured financial facts, including measurement units and reporting periods. Those distinctions inform the source fields suggested here. The proposed workbook structure is an organizational example that can be adapted to manual research; it does not require an API connection or imply that imported figures are automatically comparable.

Primary-source context: SEC: EDGAR Application Programming Interfaces.

Create a Sources sheet with a source identifier, issuer identifier, document title, URL, publication date, access date, and relevant location. Create an Observations sheet with an observation identifier, source identifier, metric or claim, period, unit, value, and a short context note. Then create a Research sheet containing questions, linked observation identifiers, interpretations, uncertainties, status, and next action.

The separation matters because one document can support several observations, and one conclusion can depend on several documents. Repeating a source link in many narrative cells makes corrections difficult to manage. Linking through stable identifiers lets you find which conclusions rely on a corrected observation. Keep identifiers meaningful enough to use but independent of row numbers, since sorting and inserting rows should not change what a reference points to.

Use a small status vocabulary such as queued, investigating, waiting for evidence, and archived. Treat the status as a workflow label, not an investment verdict. For numerical observations, distinguish zero from unavailable. A blank cell can mean many things, so add a reason field where missing information matters: not disclosed, not yet checked, inconsistent definition, or source inaccessible.

Protect formula cells from accidental editing where your spreadsheet software supports it, and keep raw observations separate from calculated outputs. Record formulas in plain language alongside important calculations. If a source changes, add a revised observation linked to the earlier one rather than silently overwriting the historical input. A correction can then flow into a new research version while preserving the evidence that informed the previous interpretation.

Imagine a workbook studying fictional Oakbridge Tools. Source S001 contains revenue of 200 currency units for a defined annual period, recorded as observation O001. Source S002 contains revenue of 230 for the next comparable period, recorded as O002. The Research sheet links both observations to question Q001: What explains the revenue change?

A calculation cell divides 230 by 200 and subtracts one, giving 15% growth. The interpretation cell says reported revenue increased 15% under the recorded definitions, while the explanation remains unresolved. The next-action cell asks for a breakdown of pricing, volume, and changes in business scope. These invented figures demonstrate the workbook relationships rather than any real company result.

Later, the researcher discovers that O002 was entered in a different currency. The observation is marked as unsuitable for the comparison, and the growth output is flagged pending repair. The workbook does not average currencies or leave the attractive percentage in the summary. Because Q001 links to the observations, the affected conclusion is easy to locate and revise.

Create stable identifiers for sources, observations, and questions. Record URLs, document dates, access dates, periods, units, and relevant source locations. Keep observed values separate from calculations and interpretations. Define status options and missing-data reasons so every row uses consistent language.

For calculations, document the input identifiers and formula meaning. Check that comparable periods and units are being used. Make errors visible rather than replacing them with zero or an empty string that hides the failure. Keep a change note when correcting an input or revising a conclusion, and preserve the earlier version when historical reasoning matters.

Before sharing an extract, remove irrelevant personal information and confirm that each displayed conclusion has a traceable evidence path. Review whether the recipient can understand the workbook without knowing your shorthand. A concise legend often adds more value than another summary chart.

Begin with an original hypothetical workbook containing source S014, observation O027, and question Q008. Source S014 is a fictional annual report. Observation O027 records a revenue figure from a named table, while Q008 asks whether the year-to-year comparison is meaningful. The identifiers express relationships independently of sheet position. If O027 moves from row twenty to row six after sorting, Q008 should still point to O027. A reference that merely says see row twenty is not a durable evidence link because the row position is a presentation detail.

Design one complete example before adding hundreds of records. The source row needs enough context to find the document and the relevant location. The observation row needs the source identifier, value, unit, period, definition, and extraction note. The research row needs the question, supporting observation identifiers, interpretation, uncertainty, and next action. Keep one observation per row when the values have different periods, units, or definitions. Packing several figures into a narrative cell makes it harder to identify which one changed and which calculations depend on it.

A worksheet-design card can list each field, its purpose, acceptable values, and whether it is required. Distinguish genuinely required evidence fields from optional administrative detail. A tiny log does not need elaborate automation to be auditable, but it does need stable relationships. If a question uses three observations, record all three links rather than linking only the most recent source. Include a readable source-location note alongside the URL, since a reader should not have to rediscover the relevant table from the document's first page. This makes the initial design useful for retrieval rather than merely attractive in a dashboard screenshot.

Consider a hypothetical company with revenue recorded as 2.4 million currency units in one period and 2,700 thousand in the next. Dividing the displayed numbers directly would compare incompatible scales. Converting both into thousands gives 2,400 and 2,700. The growth calculation is then 2,700 divided by 2,400 minus one, or 12.5%, assuming the periods and metric definitions are otherwise comparable. The conversion should be a visible calculated observation linked to the original values, not a silent edit that erases how the numbers were reported.

Add original unit, scale multiplier, normalized unit, reporting period start, reporting period end, and comparability status to the calculation worksheet. A twelve-month period and a nine-month period are not automatically comparable simply because both rows carry an annual-looking label. If a researcher chooses to annualize a partial period for an illustrative exercise, record that transformation and its assumptions separately. Do not present the transformed number as a reported figure. Likewise, a currency conversion needs a documented rate convention and date rather than an unexplained multiplier inserted to make the formula return a plausible percentage.

The worksheet should display the numerator observation, denominator observation, normalization steps, formula, and unresolved comparability questions. If the denominator is zero, mark percentage growth undefined under the chosen formula instead of replacing the error with zero. A negative denominator also needs interpretation: ordinary percentage-change language may be confusing when a measure crosses from negative to positive. Preserve the absolute change and explain what was calculated. The purpose is not to prohibit arithmetic, but to keep a correct spreadsheet formula from implying a meaningful business comparison before the units, periods, and underlying definitions have been reconciled.

Suppose a hypothetical observation O041 records operating profit of 45 from source S020. A later inspection discovers that 45 was a subtotal for a different business segment; the intended consolidated figure was 30. Keep O041 with a status explaining the extraction error, then create a corrected observation O042 linked to the same source location or its precise replacement. This preserves the difference between a source revision and a researcher correction. The company did not change the published number in this scenario; the research process initially selected the wrong one.

Imagine question Q012 uses O041 to calculate a margin against revenue of 300. Its original calculation was 15%; using O042 gives 10%. The correction affects both the percentage and any narrative describing the margin. A formula update alone is insufficient if the summary still says unusually strong profitability based on the earlier figure. Create an impact list covering dependent calculations, research notes, charts, and shared extracts. Mark each as revised or awaiting review. This links the numerical correction to its consequences instead of treating a green formula cell as proof that the entire research record is repaired.

A change worksheet should record old observation, new observation, reason, discovery date, affected questions, and interpretation change. Keep a short explanation that another reader can understand without reconstructing the original editing session. Not every typo needs a lengthy audit trail, but a correction that changes a calculation or conclusion deserves one. For historical reasoning, preserve the earlier note with its date and link to the replacement. The resulting record shows both what the researcher believed and why that belief changed, which is more informative than a workbook whose past mistakes disappear whenever a cell is overwritten.

An original hypothetical research row says revenue increased from 200 to 230. That observation supports a 15% reported increase under matched definitions. It does not by itself establish whether the cause was higher prices, more units sold, acquisitions, currency movement, or another factor. Put the observed change in an evidence field and possible explanations in a hypothesis field. A third field should state the next evidence needed. This separation prevents a plausible explanation from becoming indistinguishable from the reported facts when the workbook is revisited several weeks later.

For a deliberately simplified reconciliation exercise, suppose comparable hypothetical supporting records attribute 12 of the increase to volume, 8 to pricing, and 10 to a scope change, with no overlap under the example's definitions. The components sum to the total increase of 30. If they instead sum to 26, retain a residual of 4 and investigate the definitions or missing components. Do not label that residual organic growth simply because the workbook needs a category. A residual is the difference between the total and the recorded bridge, not an independently established economic explanation.

Build an explanation worksheet with total change, proposed driver, supporting observation, contribution, overlap warning, and unresolved residual. Some narratives cannot be decomposed into additive numerical contributions from the available evidence. In that case, use qualitative observations rather than inventing a precise bridge. A useful question can remain waiting for evidence while the reported change is complete. This distinction lets the log advance without forcing every research item to a confident conclusion. It also gives a later reviewer a clear route for challenging the interpretation: inspect the proposed driver and its evidence, rather than disputing an undifferentiated paragraph containing facts and speculation together.

Use an original hypothetical question lifecycle: queued means the question has been recorded; investigating means evidence collection has begun; waiting for evidence means a specific missing input prevents the next conclusion; reviewed means the stated research check has been completed; archived means the item is no longer active. These are proposed workbook definitions, not investment judgments. A reviewed question can still have an uncertain answer. An archived question may have been abandoned because the necessary evidence was unavailable, rather than resolved successfully. Record the reason so the status remains useful.

Give each active question a next action with an observable completion condition. Replace vague entries such as research more with locate the source table defining the reporting segment or reconcile the currency unit for O027. The action should name the missing evidence or calculation, not prescribe a transaction. Add a last-reviewed date and an evidence cutoff so another reader can distinguish recent administrative activity from recent underlying information. Updating a task status today does not make last year's financial observation current. Keep those dates separate in both the workbook and any exported summary.

Before sharing a research extract, include a compact legend, source links, measurement periods, units, interpretation dates, and material unresolved questions. A recipient should be able to follow one displayed conclusion back through its calculation to the original observation. Remove irrelevant personal notes and avoid exposing unrelated records merely because they occupy neighboring rows. The final review can focus on whether that evidence path is understandable and whether status labels match completed work. A workbook earns practical value through retrievability and honest uncertainty, not through the number of colored fields or a completion percentage that treats unresolved research as finished simply because every cell contains text.

A workbook with valid formulas can still contain incorrect source interpretation, missing evidence, or an unsupported investment thesis. Data validation controls prevent certain entry errors; they do not validate the economics of the analysis. Likewise, a linked official document establishes where a number came from, not that your chosen comparison is meaningful or that your conclusion follows.

Keep the workbook's purpose modest and explicit: organize research evidence, preserve decisions, and identify next questions. It is not an automatic investment recommendation engine. As the log grows, simplify redundant fields and archive resolved questions so maintenance remains manageable. The useful outcome is a record that another reader can follow from source to observation to interpretation, including the uncertainties and corrections that a clean-looking dashboard might otherwise conceal.