Prepare Historical Price Data for Backtesting

Raw price files are rarely ready for research. These are the cleanup steps that matter, in a sensible order.

Open the Data Transformation

Quant researchers know the problem well: a vendor file can arrive with dates in two formats, rows in the wrong order, the same bar twice and blanks where a value is missing. A backtest will run on any of it and give a result you should not trust.

Profile the file first with the Data Profiler. Note the column types, blanks and duplicate rows. Then use Data Transformation. Change the date format to one standard. Sort rows by time. Remove duplicate rows, and use Remove empty values for rows with no data. Trim spaces in symbol columns so that one instrument is not split into two.

Be honest about what the tool does not do. It changes formats but it does not convert time zones or adjust prices for splits and dividends, so do those in your research code or with a data source that provides adjusted values, and write down which you used. Decide how to treat gaps: a missing day can be a holiday or a data fault, so check before you fill.

Finish with a check. Compare the row count and the first and last dates with the original, and keep the original file. Data Lineage records the steps you applied, which helps when a result is questioned later. This article is about data preparation and is not investment advice.

Open the Data Transformation

Related tool pages

  • Delete Columns from a CSV File: Remove one or more columns from a CSV or Excel file and download the result. Free, in your browser, and your file is never uploaded.
  • Reorder Columns in a CSV File: Drag columns into a new order, or send a column to the start or the end, in a CSV or Excel file. Free, in your browser, nothing is uploaded.
  • Remove Empty Rows and Columns from CSV: Remove rows with empty cells, rows that are completely empty, or columns with no values from a CSV or Excel file. Free, in your browser.
  • Find and Replace in a CSV File: Find and replace text in a column of a CSV or Excel file, then download the result. Free, in your browser, and nothing is uploaded.
  • Change Date Format in a CSV File: Change the date format of a column in a CSV or Excel file, for example 09/03/2026 to 2026-03-09, or get only the year or month. Free, in your browser.
  • Convert Excel Date Numbers to Dates: Turn Excel date numbers such as 46090 and unix timestamps into readable dates in a CSV or Excel file. Free, and your file stays in your browser.

How to use the Data Transformation: step by step

  1. Profile the file in the Data Profiler.
  2. Open Data Transformation and add Change date format and Sort rows.
  3. Add Remove duplicate rows and Remove empty values.
  4. Save the result and compare row counts and dates with the original.

When to use the Data Transformation

  • Clean a vendor price history
  • Standardise dates and sort by time
  • Remove repeated bars
  • Keep a record of the preparation steps

Example

Input: plans.csv + customers.csv, key = id

Output: id, first_name, plan, amount

Frequently asked questions

Which transformations are available?

Join with a second file (inner, left, right, full), trim spaces, concatenate columns, change case, find and replace, split a column, rename, remove columns, reorder columns, remove empty rows or columns, filter rows, remove duplicates, sort and fill empty cells.

Can I run several steps?

Yes. Steps run from top to bottom and each one sees the result of the one before. You can reorder or remove them.

Can I join two files or combine columns?

Yes. Data Transformation lets you join two CSV or Excel files on a key (inner, left, right or full), and trim spaces, concatenate columns, change case, find and replace, split, rename, filter, sort, remove duplicates and fill empty cells. You choose the steps, check the result and download it. Your files stay in your browser.

Can I change the date format of a column?

Yes. Add a Change date format step, pick the column and the new format, such as 2026-03-09, 09/03/2026 or March 9, 2026. It reads many layouts, Excel date numbers and unix timestamps, and can also give only the year, month, weekday or quarter. If 03/04/2026 is unclear, you choose day first or month first.

Tip: Write down what you did about time zones and price adjustments. The tool does not do either.