Turning a messy investment record into data that tells the right story

The investment analysis toolkit: two tools I designed and built to test how well I could bring data handling and financial analysis together. The Investment Decision Record cleans the messy data a real investor produces and grades each decision against a plan written in advance. The Thesis Builder is where the reason for a position gets written down, next to short courses on the investing philosophies those reasons come from. A set of Monte Carlo simulations turns price and volume history into a range for where an index future could close next.

Sponsor
Self-directed · product design and architecture
Status
Built · simulation ranges checked, and too narrow
Governing constraint
Methodology Silence
Reading time
13 minutes

Design, not dollars The record behind this toolkit is my own. No dollar figure, account balance, return or behavioral finding from it appears here. The method stays private too: the real Rules, their thresholds and their definitions are withheld, which is what Methodology Silence means on this page. Every Exhibit uses the Illustrative Investor, an invented investor with a synthetic account over a synthetic period, whose Rules A to D stand in for the real ones. The counts below describe the data I handled, not how the account did.

What this Engagement produced

  • 231 executions cleaned

    Read from the broker’s own export across 37 market sessions, then rebuilt, de-duplicated and graded.
  • 5 broker export formats

    Read without a separate parser for each, by matching their column headers to one set of fields.
  • 5,000 simulated closes per range

    Behind each next-day range for an index future, reported as a range and never as a single price.

The Situation

I built this toolkit to test how well I could bring data handling and financial analysis together. The test case was a single futures account, my own, because it produced exactly the kind of messy data real investors produce.

That data came from four places that never agreed with each other. Some brokers export one row per completed position and others one row per fill, so a single decision can arrive as three partial fills. The charting platform and the broker keep different clocks, and some timestamps carry a timezone while others do not. Contract names change every quarter, so the same index future shows up under a new code every few months. And the reasoning behind each decision was scattered across hand-written notes, a notes app and folders of chart screenshots.

None of those sources is wrong on its own. Put together without cleaning, they tell the wrong story. A duplicate row doubles a loss, a timestamp read in the wrong zone moves a decision onto the wrong day, and a plan graded from memory says the plan was followed. The point of the project was to get the data into a shape where it tells the correct story, including when that story is unflattering.

What I Did

The work had four parts: cleaning the raw record, grading the plan against it, building a place to state the thesis behind each position, and simulating where prices could go next.

1. Cleaning the raw record. Every import runs the same steps before anything is graded:

  • Match the columns. Each broker names its columns differently (“B/S”, “Buy/Sell”, “Side”), so headers are matched to one fixed set of fields instead of writing a parser per broker. That covers five export formats, and a manual column map handles anything the matching misses.
  • Rebuild positions from fills. When an export lists single fills, opening and closing fills are paired first in, first out, so partial fills become one position with one entry, exit, size and fee.
  • Put every timestamp on one clock. A timestamp with no timezone is read as the investor’s local time, the clock they actually saw, and one with a timezone is read as an exact instant. The day a decision lands on decides which version of the Rules grades it, so this matters more than it looks.
  • Normalize symbols and money. Contract codes reduce to one root, so MNQH5 and the platform’s long contract IDs both become MNQ. Money cells lose their dollar signs and commas, and accounting parentheses read as negatives. Fees stay a separate field, because a fee the record hides is a cost the investor is told they never paid.
  • Remove duplicates without deleting real data. The broker’s own ID decides what is a repeat. Where an export has no ID, the fallback key adds a counter, because my real history held eight pairs of separate executions that were identical in every field, and a simple de-duplication would have deleted them without a word.
  • Attach old notes to the right row. Notes written before the importer existed are matched to broker rows on date, symbol, direction and entry price within one tick. Time is left out, because a remembered time is unreliable. A missed match leaves two rows I can see and fix, while a wrong match quietly moves my reasoning onto a different decision, so the bar is set high on purpose.
  • Skip bad rows, never the file. A malformed row is counted and set aside, so one broken line does not sink an otherwise good import.

The result was 231 executions across 37 market sessions, each one a clean row the rest of the toolkit could rely on.

2. Grading the plan against the record. The investor writes their Rules down before a market session, each as a condition a computer can check. Afterwards, the Investment Decision Record checks every execution against them and marks each Rule kept or broken for the day. That is Verified Adherence, and nothing the investor types reaches it.

I kept the investor’s own grade instead of deleting it. Claimed Adherence, the investor’s account of the same day, sits beside the computed figure, and the difference is shown for review instead of reconciled away. The record knows what happened but never why. The investor knows why, and has every reason to be generous. The gap shows where belief has drifted from behavior, and averaging the two into one score is the one step that hides it.

Exhibit 1 Averaging what was done with what was reported erases the signal worth reviewing.

Read each Rule across the week, the record beside the report, then press Report one combined score.

View the data
A synthetic week of the Illustrative Investor, each Rule graded for each day from the broker’s record and as reported in review
DayRuleVerified AdherenceClaimed Adherence
MondayRule A, position sizeKeptBroken
MondayRule B, entriesKeptKept
MondayRule C, exitsKeptKept
MondayRule D, when to stopKeptKept
TuesdayRule A, position sizeKeptKept
TuesdayRule B, entriesKeptKept
TuesdayRule C, exitsBrokenKept
TuesdayRule D, when to stopKeptKept
WednesdayRule A, position sizeKeptKept
WednesdayRule B, entriesBrokenBroken
WednesdayRule C, exitsKeptKept
WednesdayRule D, when to stopKeptKept
ThursdayRule A, position sizeKeptKept
ThursdayRule B, entriesKeptKept
ThursdayRule C, exitsBrokenKept
ThursdayRule D, when to stopKeptKept
FridayRule A, position sizeKeptKept
FridayRule B, entriesKeptKept
FridayRule C, exitsKeptKept
FridayRule D, when to stopBrokenKept
Source: illustrative, a synthetic week of the Illustrative Investor. Each Rule on each day is graded from the broker’s record (Verified Adherence) and by the investor in review (Claimed Adherence). Kept or broken is a simplification for this Exhibit, and the combined score is the plain average of both counts.

3. The Thesis Builder. This started as a rule book and became a place to state why a position is being taken, before it is taken. Each position carries the investor’s thesis: the strategy behind it, the criteria that had to be true, the evidence that lined up, and the planned exit and stop. The broker cannot know any of that, so an import never writes over it.

Next to the thesis sit six short courses on the philosophies a thesis can come from. They run from the long-term value approach of Benjamin Graham, Warren Buffett and Charlie Munger, with compounding, moats and margin of safety, to statistical methods built on standard deviations, along with supply and demand, breakouts, Fibonacci levels and a liquidity-based method. The idea is that an investor learns a philosophy by writing theses in its terms, not by reading about it once.

The charting side supplies the evidence. Its indicators mark price levels on the chart, such as unfilled gaps and clusters of equal highs and lows, so they do not have to be redrawn by hand every session. They present evidence and never give a direction, because the direction is the investor’s call. What a chart showed at a point in time can be exported as structured data and attached to the thesis it supported. I rebuilt those indicators from an inherited script that did a fraction of what I needed, and the first step was writing down exactly how it failed, which turned a vague plan to rebuild into fifty numbered requirements.

4. Monte Carlo simulations. The last part asks where prices could go next, and answers with a range. I pulled 90 days of hourly prices and volume for Nasdaq-100 index futures from a public source and cleaned them the same way: missing rows dropped, every timestamp on one clock, hours rolled up into days. From the open, high, low, close and volume I built a few features: the day’s range, the opening gap, whether volume spiked above its recent norm, and the last five daily returns.

Two simulations run on that data. The first draws 500 paths of the next 24 hours, hour by hour, from the period’s average move and volatility, which is the shape Exhibit 2 draws. The second estimates the next day’s close. It draws 5,000 returns from the last 20 days, weighting recent days more heavily, scales them to current volatility, and centers them on a random-forest model’s estimate of the next day’s return. What comes out is a range: the middle half of the simulated closes, from the 25th to the 75th percentile, with the median between them.

Exhibit 2 One simulated path looks like a forecast, and the full fan shows it is one draw from a range.

Read the shaded ranges hour by hour, then press One path to see what a single simulation would suggest on its own.

View the data
5,000 synthetic paths of an invented index over the next 24 hours, starting at 100: the simulated level at each percentile every three hours, and one path on its own
HourOne path5th percentile25th percentileMedian75th percentile95th percentile
0100.0100.0100.0100.0100.0100.0
399.699.399.7100.0100.3100.7
699.999.099.6100.0100.4101.0
9100.298.899.5100.0100.5101.2
12100.098.699.499.9100.6101.4
1599.798.499.399.9100.6101.5
1898.798.299.299.9100.6101.6
2198.898.199.299.9100.7101.9
2498.697.999.199.9100.8102.0
Source: illustrative and synthetic. 5,000 paths drawn from hourly returns with no drift and a volatility of 0.25% an hour, starting from an invented index level of 100. No real price, date or position is drawn.

Those are ranges, not predictions. A single path looks like a forecast, which is exactly why the Exhibit draws the whole fan. The same idea runs inside the Investment Decision Record’s practice mode, which generates new synthetic price bars fitted to a real market’s volatility patterns, so practice happens on price action nobody has memorized. The next step is to point the range at individual stocks and ask whether a stock’s current price sits favorably inside it.

Three design decisions hold the record up.

One calculation produces every number. The grading is a single function. It takes the executions, the Rules, the account, the investor’s notes and the date as inputs, and returns every number on every screen. Because the date is an input, re-running a session from March today gives the answer it gave in March, and every screen shows the same answer.

Rules are versioned. The investor has to be able to edit their Rules, or they are the system’s rules and not the investor’s. But a threshold edited in place rewrites its own history, and last month’s grade quietly becomes a claim about this month’s rule. So every edit saves a new version, and every day records the version it was graded under.

Exhibit 3 A revised rule governs the decisions after it, and earlier decisions keep the rule they were made under.

Read the week as revised, then press Edit in place and watch what happens to the days already graded.

View the data
The synthetic week of Exhibit 1 for Rule B: the version each day was recorded under, and the day graded under each version of the Rule
DayRuleRecorded underGraded under version 1Graded under version 2
MondayRule B, entriesVersion 1KeptKept
TuesdayRule B, entriesVersion 1KeptBroken
WednesdayRule B, entriesVersion 1BrokenKept
ThursdayRule B, entriesVersion 1KeptKept
FridayRule B, entriesVersion 2BrokenKept
Source: illustrative, the synthetic week of Exhibit 1, for Rule B only, of the Illustrative Investor. Rule B is revised on review after Thursday and saved as version 2, and the system grades each day only under its own version, matching Exhibit 1. Both versions are shown here so an edit in place can be drawn. What either version of Rule B says is withheld.

The broker connection reads and never writes. The system knows when the day’s budget is spent, so it could block the next order instead of only objecting to it. I rejected order placement, because a block works only if it cannot be bypassed, and the broker’s own platform is one keystroke away. It would buy no enforcement while adding two failure modes: a bug that moves money and a failed request that leaves a position open. When the read-only connection fails, the system says so loudly, because a stale count shown as a current one gets believed.

Exhibit 4 The system observes every execution and places none.

Read the paths as built, then press Connection failed to see what the Illustrative Investor is shown when the record stops arriving.

View the data
The paths between the broker and the Investment Decision Record for the Illustrative Investor’s account, whether each was built, and how each fails
PathFromToBuiltFailure mode
Read the execution recordThe brokerThe recordYesThe connection fails and the record goes stale
Place an orderThe recordThe brokerNoA bug that moves money
Place an orderThe recordThe brokerNoA failed request that leaves a position open
Source: illustrative, the paths between the broker and the Investment Decision Record for the Illustrative Investor. The order path was not built, so its failure modes, named above, are drawn struck through. The notice is worded for this Exhibit, not copied from the system. The broker’s name, how the system signs in to it, and every detail of the account are withheld.

What Changed

My notes stopped being a place to type in what happened and became a record built from data. Once executions arrived by import, writing meant adding the thesis to rows that already existed, limited to what the broker cannot know. The headline figure changed with it, from how completely a form was filled in to how much room the account has left before it ends.

Reviews changed the same way. A week’s review became reading clean records instead of opening dated folders of screenshots.

Pending Result Whether the simulated ranges hold up is the open question. A range is calibrated when the real close lands inside the middle half about half the time, and inside the 5th to 95th percentile band about 90% of the time. I ran that check on the 65 days the saved data allowed, from July 24 to October 10, 2025, rebuilding each day’s range from only the data before it. The middle half caught the real close on 28 of 65 days, 43%, and the wider band on 48, 74%. So the ranges run too narrow, and the simulation is more confident than the market. The median pointed the right direction on 34 of 65 days, close to a coin flip. Sixty-five days is too few to settle it either way, so the next step is the same test over years of data, widening the range until it earns its confidence. Whether grading decisions against the record changes them is a separate question, and answering it would take a long run of decisions this page does not publish.

Withheld What the committed Rules would have done across the account’s history has been computed exactly, from executions that already happened. It is not on this page. That figure is a personal profit-and-loss number. A number about my own account is not evidence about my engineering, and it is what a reader would remember instead. A check in the site’s build now fails if a dollar figure of that kind appears in any page. So this is enforced by the build, not left to my restraint.

What I Learned

The hardest call in this project was choosing which story the data is allowed to tell. A messy record will support almost any story if you let it. Drop the duplicates you do not like, read the timestamps in whichever zone moves a bad decision onto another day, or average the investor’s grade with the computer’s, and the record says what you hoped. Every cleaning step above is a decision about what the data may say, so each one is written down with its reason, in the code, where anyone checking a number can find it.

I learned the second lesson by getting it wrong. I wrote the record’s alerts from a story about the user, not from the user’s data. When I finally pointed the same import that produces Verified Adherence at the alert list, one alert turned out to be watching for a pattern the record did not contain, and I retired it. The charting side taught the same thing from the other end: a readout dead from the day it was written sat on the chart for months, unnoticed because nothing depended on it. Checking the simulation for this page did it a third time. My original app read the price columns in the wrong order, so its “close” was each hour’s open. “I use it and it works” is not evidence that it works.

What this page claims, and what it does not

Nothing here should be read as a claim that the toolkit made anyone a better investor. Returns are out of scope, and the Withheld note above says why.

What the page claims is narrower and checkable. The grade comes from a record the investor does not write. The calculation gives the same answer on old data. The simulated ranges were checked against what happened, on a sample too small to settle it. And the decisions behind the build were written down with their alternatives at the time, not reconstructed afterwards for a write-up.

Where Else This Applies

Clean the record before analyzing it. Any analysis that joins data from systems that were never designed to agree, like a loan system and a customer database, or two platforms on different clocks, inherits every disagreement between them. The cleaning rules are the analysis’s first assumptions, and they deserve the same scrutiny as the model.

Give ranges, not point forecasts. A forecast given as one number invites people to treat it as a promise. A range, together with how often the real outcome landed inside it, says how much the forecast should be trusted, and that check is cheap to run.

Self-report measures intention, and it fails hardest where it matters most. Control attestations, training completion, time recording and safety checklists all ask the person who did the work to confirm they did it properly. Each is least reliable in exactly the cases worth catching. Where a system already holds a record of the behavior, compliance can be computed from it. The distance between claim and record is then a second measurement, and most organizations throw it away by reconciling it. The same reading works for an adviser, whose recorded rationale can be read against the client’s executions, and for an investment committee, whose policy statement can be read against the portfolio’s transactions.

All six Engagements Ask me about this one