About the project
Asset classes on the same starting capital
The backtester shows how the same starting capital would have developed in equity indices, Bitcoin, gold, property, bonds, commodities and cash markets. Visitors choose a period, capital and comparison series. The page calculates ending value, return, CAGR, drawdown and other risk metrics from the stored observations.
The browser page does not call financial providers. PHP loads the values from MySQL, calculates the comparison series and returns a fixed JSON format. API keys, raw responses and provider logic therefore stay on the server.
Features
What the backtester calculates
- Market overview: Sort the table by return and the other metrics. Unavailable values remain at the bottom.
- Flexible periods: From one week to ten years, the maximum period, custom start and end dates, and a draggable period slider.
- Individual and total views: Every asset class has its own path. In the total chart, the highest-return line appears on top when hovered.
- Logarithmic scale: All Y-axes remain positive and keep relative changes readable over long periods.
- Income and costs: Dividends contribute to total return. Net rental cash flow is limited to the individual property paths. The TER switch reveals its inputs only when enabled.
- Taxes and real returns: The comparison starts nominally and without a tax model. Settings can add German, European or US inflation, a custom annual rate and tax calculations.
- Actual data dates: Tooltips and tables show the last trading day that was actually observed.
- Failure tolerance: A provider error does not delete history. Visitors continue to see the latest stored values.
Data pipeline
How data reaches the backtester
Indices, Bitcoin, gold and funds
CoinGecko provides Bitcoin in euros, FRED provides the S&P 500 and GoldAPI provides the gold price. MSCI World, DAX, EURO STOXX 50 and the fund proxies for global bonds and commodities use separate Yahoo parsers. USD and GBP values are converted into euros with the matching ECB reference rate.
Property, cash markets and inflation
US property prices come from the FHFA through FRED; German property prices and European inflation come from Eurostat. The ECB provides the compounded €STR index, the New York Fed the SOFR index and the BLS the US consumer price index.
Dividends and new releases
The S&P 500 dividend yield is imported monthly from the authorised Multpl table. Stored annual values supplement MSCI World, DAX and EURO STOXX 50. Each run checks only the relevant lookback so late releases and revisions can arrive without replacing the imported history.
Technology
Architecture and operation
Backend: Provider adapters return a shared observation format. A validation service checks dates, currencies, positive values and duplicates. Repositories encapsulate database queries; services handle synchronisation and calculation.
Database: Separate tables store sources, asset classes, observations, dividend values, sync states and logged runs. Indexes speed up range queries and the search for due jobs.
Frontend: Vanilla JavaScript manages the period slider, return sorting, comparison series and the inflation, TER and tax switches. Optional models remain off after a fresh page load. Chart.js is served locally; live regions and textual chart summaries complement the diagrams.
Operation: STRATO provides PHP 8.3 and MySQL 8.0. FileZilla transfers files, SSH starts tests and imports, and an external HTTPS scheduler calls the daily update endpoint with a secret header.
Skills used
What I built
Requirements analysis
I broke the specification into concrete tasks for data, API, interface, security and operation. Features without a finished calculation were deliberately disabled.
Data engineering
I combined minute, daily, trading and exchange-rate data by date, time zone and revision. Checksums protect the completed CSV files.
Financial methodology
I calculate price performance, dividends and rental cash flow separately. Inflation, TER and taxes apply only after an explicit selection; foreign-currency values use that day’s reference rate.
PHP and APIs
I split the PHP backend into providers, domain objects, repositories and services. Public endpoints validate every input and return a consistent JSON format.
MySQL
I built the MySQL schema with idempotent upserts, source revisions, date indexes, sync cursors and expiring locks.
Debugging
During debugging, I fixed STRATO CGI differences, a PDO parameter error, an incorrectly configured CoinGecko key and unclear HTTP errors.
Testing
I wrote tests for returns, CAGR, drawdown, dividends, rental cash flow, inflation, TER, taxes, parsers, CSV imports, request security, backoff, locks and logarithmic charts.
Deployment
I deployed the application to shared hosting without a build system. Uploads, database imports, server tests and daily updates run through SFTP, SSH, phpMyAdmin and an external cron service.
Problem solving
Issues uncovered during development
- MySQL without an active database: The schema import succeeded after explicitly selecting the target database.
- STRATO CGI instead of a classic CLI: Test output and argument parsing work without STDERR, php -r or -f before the script arguments.
- PDO parameter error: The sync no longer reuses named parameters multiple times.
- Missing gold dates: The official GLD archive added 130 trading days without switching the data series.
- No hosting cron job: An external service calls the protected HTTPS endpoint every day.
Looking back
What I learned from this project
A financial chart is only the final step. Most of the work lies in choosing sources, forming daily observations, converting historical currencies, respecting data rights and importing updates without damaging existing values.
Shared hosting has its own constraints. PHP does not always behave like a local CLI, and a missing hosting cron job needs a secure replacement. The application had to remain testable and maintainable under those conditions.
Live sources extend the imported history and must not overwrite existing data. Daily, monthly and quarterly series therefore follow separate validation intervals.