Data analysis
A dashboard can look clean and still produce wrong answers. Before the first chart, provenance, structure, units, time ranges and metrics must be defined. This guide covers the checks I use before analysing data.
The question
The chart comes late. First decide what the metric is supposed to measure. “How is my portfolio doing?” could mean the account balance, investment performance, portfolio value including deposits or real return after inflation. Every one of those can be calculated correctly and still answer a different question.
Before analysing, write down at least the observation, period, unit and rule. A monthly portfolio series needs a definition for whether 2026-01 means the start of the month, its closing value or an average.
| Target metric | What counts as an observation? | Unit |
|---|---|---|
| Portfolio value | Account value at a defined cut-off date | EUR |
| Period return | Change between consecutive cut-off dates | Decimal internally, e.g. 0.05 |
| Cumulative return | Change from the first to the last value | Percent |
| Deposit | Cash entering the portfolio from outside | EUR |
Context
A value alone is rarely enough. An observation needs its source, retrieval time, date or period, time zone, unit, identifier and the rule that produced it. Without that context, an export may look complete but cannot be interpreted safely later.
| Check | Question before analysis |
|---|---|
| Source | Who supplies the value, and is it an original series, a proxy or an estimate? |
| Time | Which date and time zone apply? Is it daily, monthly or quarterly data? |
| Granularity | Does one row represent one day, one month, one security or an existing sum? |
| Unit | Is the value EUR, USD, percent, percentage points, units, grams or an index level? |
| Definition | Is it a price, return, price index, total-return value or forecast? |
One row per day cannot automatically be compared with one row per month. Combining data requires a deliberate aggregation. A month-end value, monthly average and sum of daily returns are different quantities.
The asset-class backtester therefore defines exactly which series are compared. S&P 500, Bitcoin and gold sound unambiguous in conversation; in the model they are specific series with their own currency, trading calendar and source.
Data pipeline
I separate raw data from the cleaned working file. The raw file remains exactly as delivered. The cleaned file contains consistent dates, column names and number formats. This makes it possible to distinguish an error in the source from an error introduced during conversion.
A concrete example from the data work behind my backtester: some supposed CSV requests returned HTTP 200 while the content was an HTML page. Checking only the status code marks the download as successful and moves the failure to the parser or dashboard.
Units
The most common error is not the formula but the representation before it. A 5% return may be stored as 0.05 and displayed as 5.0%. Both describe the same value, but only one belongs in a function expecting a decimal.
| Field | Internal representation | Display |
|---|---|---|
| Return | 0.05 | 5.0% |
| Share | 0.25 | 25% |
| Price | 123.45 | €123.45 |
| Exchange rate | 1.08 | 1.08 USD per EUR |
Percentage points, basis points, price versus return, nominal versus real values and different currencies add further traps. Each field needs a precise definition. Percent signs belong in the display, not in a column whose parser expects a number.
The fastest counter-check is a known record. Take one value from the raw file through to the display and compare every intermediate step. One test case often finds decimal errors earlier than a complete dashboard screenshot.
Missing values
A genuine zero is an observation. A blank usually means that no observation exists. These cases must not receive the same meaning.
Suppose a portfolio is €10,000 in January and €10,500 in February, but March is missing. Replacing the gap with a 0% return makes March look stable. Carrying forward €10,500 makes the chart look complete even though no new March observation exists. Both replacements change the analysis.
This distinction is central to the asset-class backtester. Exchanges close at weekends while Bitcoin trades continuously. The shared chart carries forward the last known value, but risk metrics use actual observations so weekends do not become extra days with 0% returns. The full method is documented here.
Uniqueness
A row with a date and value is not automatically a unique observation. The key must fit the model. For a daily price series it might be series plus date; for a portfolio with multiple accounts, the account ID is part of the key. For monthly data, decide whether a later source revision replaces the old value or becomes a new version.
| Error | Possible effect | Clean response |
|---|---|---|
| Duplicate date | A day or month receives too much weight. | Check the key, decide on the revision and log the difference. |
| Descending time | Returns are calculated in reverse order. | Parse dates and sort ascending before calculating. |
| Locale error | 1,234.56 is read as 1.23456 or 123456. | Define decimal and thousands separators for the source. |
| Source revision | Old and new versions remain in one series. | Mark the version and select exactly one valid observation. |
Do not automatically average two values for the same day. That would be a new business rule and could hide an error. First establish whether a source was corrected, two venues were combined or the date was assigned incorrectly.
Plausibility
A value can parse as a number, be positive and sit in a normal range while still having the wrong meaning. Plausibility checks therefore need two levels: technical errors that stop an import and domain warnings that require review.
| Check | Example | Decision |
|---|---|---|
| Technically invalid | Date unreadable, required column missing or content type wrong. | Stop the import and report the error. |
| Structurally invalid | Duplicate key without a resolved revision. | Hold the record; do not silently average it. |
| Domain warning | Unusual jump, long gap or stale last observation. | Mark and review; do not overwrite real jumps automatically. |
| Definition check | Forecast mixed into a series of historical actuals. | Separate it and label it in the dashboard. |
Thresholds are signals, not truths. A price can jump sharply in one day, and a nutrient value can fall outside an expected range because a new product was added. The check should focus attention, not force the data to fit.
Metrics
For a monthly portfolio series, the average of monthly returns is not automatically the annual return. Returns compound along the portfolio path. The metric also needs to know whether deposits and withdrawals are included.
A small example shows the difference: a month at +10% followed by a month at −10% has an arithmetic average of 0%, but the value path fell: 1.10 × 0.90 − 1 = −1%. The dashboard must say which quantity it shows.
If €1,000 is added during the series, ending value / starting value − 1 mixes investment performance with the deposit. A return analysis then needs a cash-flow-aware definition, such as time-weighted or money-weighted return. The formula cannot decide that for you.
Before the chart
For a small dashboard, a review page or a short log with clear fields is often enough. The important thing is that a bad import becomes visible before it is passed on as a colourful chart.
My projects
In the asset-class backtester, the work starts before the curve: choose a concrete series, define currency and exchange rate, respect the trading calendar, separate missing observations and disclose the data date. The chart can only show what those rules allow.
In the nutrition calculator, the failure point is different but the rule is the same. Price and nutrients refer to a defined basis. Functions such as foodPrice(f), UNIT_META and buildModel() depend on it. If a value stored per 100 g is interpreted as a package, the solver can produce a mathematically valid plan that is conceptually wrong.
A dashboard and a solver look different, but before calculation both need clear answers: What does one row mean? What unit is the value in? Which observations are missing? Which rule decides whether a deviation is acceptable? Only then is it useful to decide how the result should look.
Choose a period and asset classes, then read series, ending value and risk together with the methodology.
Read next