Data Quality Checks Before You Run a Backtest
A backtest is only as good as its input. A short check of the file can save days of chasing a strange result.
Quant researchers know that results that look too good, or too odd, often come from the data and not from the idea. Duplicate rows, missing days, a price that is ten times its neighbours or a column read as text can all change a result without any error message.
Start with the structure. Does the file have the columns you expect, with the right types? A timestamp should be a date and volume a number. Open the file in the Data Profiler, which shows the type of each column, the number of blank cells and duplicate rows, the minimum, maximum and average of number columns, and unusual values.
Then check the content. Look for duplicate timestamps for the same instrument. Look at the minimum and maximum of each price column and ask whether they make sense: a price of zero or a negative price needs an explanation. Check that high is not below low and that volume is not negative. These rules need your own check on top of the profile, so write them down and rerun them on every new file.
Gaps need a decision. A missing day can be a holiday, a data outage or a deleted row. The profiler shows blanks, but only you know which are normal. Decide whether to fill, drop or flag them, and record the choice.
Keep the evidence. Save the profile report with the file, and use Data Lineage if you transformed the file, so you can show what was done. A profile cannot prove that the data is right. It finds common problems early. This article is about data handling and is not investment advice.
Related tool pages
- CSV Data Quality Checker: Check a CSV for missing values, duplicates, outliers and wrong types in seconds, and share a report. Free, in your browser.
- Check a CSV for Duplicates and Blanks: Find duplicate rows, empty cells and odd values in a CSV before you use it. See a report per column. Free, in your browser.
- CSV Data Profiler: Profile a CSV in seconds: column types, missing values, duplicates, outliers and distributions, with a shareable report. Free, in your browser.
- Open a Large CSV File Online: Open and explore a CSV too big for Excel: see rows and columns, filter with SQL and export. Works for 500 MB and more, in your browser.
- CSV Viewer Online: Open a CSV file online and see its rows, column types, blanks and duplicates at a glance. Free, private, no upload.
- Data Profiler Online: Profile a CSV, Excel, JSON or Parquet file online: rows, types, missing values, duplicates and statistics per column. Free, private, large files too.
How to use the Data Profiler: step by step
- Open the Data Profiler and load the file.
- Check column types, blanks, duplicates and the minimum and maximum of each number column.
- Test your own rules, such as high not below low.
- Save the profile with the file and note the decisions you made.
When to use the Data Profiler
- Check a vendor file before research
- Find duplicates and blanks in a price history
- Spot impossible values such as negative volume
- Keep a record of the checks you ran
Example
Input: id,name,age 1,Ada,36 2,Alan,41
Output: age: integer, 0% missing, mean 38.5, min 36, max 41
Frequently asked questions
What counts as a duplicate row?
A row identical to an earlier one in every column.
What does the Data Profiler show?
Column types, missing values, duplicates, outliers and basic statistics, so you can judge the quality of a file at a glance. You can download the report, with an optional password.
Tip: Write your checks down and run them on every new file, not only the first one.