Forward vs Backward Price Adjustment
Version 1.0 · Excel workbook · what your price history costs you, under each convention
Almost every price history in this industry is adjusted backward, anchored to today, which means every corporate action rewrites the entire series behind it. Most firms did not choose that. They inherited it from a charting tool and never wrote down which convention they were on. This workbook builds both anchors side by side from the same raw data and prices the difference. Eight tabs, 197,642 live formulas, no macros, no protected sheets, nothing computed elsewhere and pasted in as a value.
The one thing it is built to do
Show you what your own anchor costs, in rows rewritten and in basis points lost.
It runs on two inputs. A date and a raw unadjusted close, and a list of corporate actions. No vendor factor is required and none is compared against, so it works off any price source you can export. Everything else, both anchors, the restatement, the rewrite counts and the precision damage, is derived in front of you from those two tabs.
What is in it
The factor, built as a share count. Start with one share. A split multiplies it, a dividend reinvested at that day's close adds a fraction to it, and the adjusted value is that day's close times the count you held that day. That is not a price, it is what your position was worth. The tab carries the forward anchored count and the same count normalized to today side by side, so the only difference between the two conventions is visible in two adjacent columns.
A restatement test. Pick a date a report was published and a historical date it quoted. The workbook shows that value as it would have read then against how it reads now, under each method. On the shipped data a December 2006 value published in December 2016 read $48.56 then and $37.26 today, down 23.26 percent, while the forward figure is identical in both.
The rewrite count, which is the operational cost. Historical rows rewritten under each method, event by event, with a chart. Then scaled to a book of instruments.
The rewrite error, which is the accuracy cost. For the common implementation that scales the stored adjusted price rather than recomputing it from raw, because a system that kept only adjusted prices has no other option. Each rewrite rounds an already rounded number, and the tab tracks one historical date through every corporate action to show what accumulates.
A precision tab with live levers. Set the decimal places your system stores and whether it rounds or truncates, and watch the damage recalculate across the full history. Truncation is the setting that matters, because it always errs the same direction and therefore compounds instead of canceling.
The data it ships with
Johnson & Johnson, January 2, 1962 to September 17, 2026. 16,286 daily rows, 259 cash distributions, 7 splits, and 20 vendor forecast rows included and explicitly excluded, because loading those by accident ingests years of projections as though they had already happened.
Sixty five years is the point rather than a flourish. The most serious failure in here only becomes visible on a long history, and a thirty year window hides it completely.
The Prices and Events tabs both carry a source column, because a tool about data integrity should say what was done to its own inputs. The 1996 to 2026 portion is observed raw closes and full distribution records from a professional data vendor, untouched. The 1962 to 1995 portion is reconstructed from Yahoo Finance, whose published Close and Dividends are already restated into today's shares, so both columns were multiplied back up by the splits that followed them. Nothing was snapped to a tick grid to make it look tidier than it is, which is why a 1962 close reads $96.50016 rather than $96.50.
What it does not do
It does not handle spin-offs, and neither does anything else. A share count of one company cannot express owning shares of a second one. That is a limit of the representation rather than a gap in the data, and the Read Me tab says so.
One security at a time. Portfolio aggregation is a different problem.
It does not model taxes, withholding, or the cost of actually reinvesting a dividend.
It does not name or rank a data vendor. It is not a vendor comparison.
It disproves the loose version of its own argument
The Restatement tab measures the return between two dates three ways, forward anchored, backward anchored as published then, and backward anchored as recomputed today. All three agree, and the workbook reports the largest difference between them as exactly zero.
That is in there on purpose. Backward adjustment does not give you wrong returns, because the anchor appears on both sides of any ratio and cancels. The version of this argument that claims otherwise is wrong, and the first numerate person in the room will say so. What changes is every stored level, and the cost of that is what the other tabs measure.
A tool that could only support its author's conclusion would be a sales sheet.
Verification
The share count was reimplemented independently in Python, written from the specification rather than from the workbook formulas, and compared to the workbook across all 16,286 rows. The two share no code. Share counts agree to 4.9e-15, forward adjusted values to 5.2e-10, backward adjusted values to 5.4e-13. The backward factor lands on exactly 1.0 at the final row and the backward adjusted price there equals the raw close, which is the closure test the whole construction has to pass.
The reconstructed portion of the data was validated before any of it was used, against the vendor data on the thirty years where the two sources overlap. Prices agree exactly on 7,661 of 7,727 rows; the 66 that do not are the two sources disagreeing on a close during the move off fractional pricing, the largest by $1.69. Dividends agree on 127 of 127 with no misses. The 132 dividends before 1995 have no second source and are unverified, which is stated on the Read Me tab rather than left for you to discover.
All 197,642 formulas recalculate without error.
The version number is on the Read Me tab. Check it against the version at the top of this page to see whether your copy is current. The form asks for an email so I know who is using the workbook. There is no mailing list.
