CSV exports and the rounding they hide

[Fairman]

CSV exports and the rounding they hide

Saving data to a CSV seems harmless. But spreadsheets and some export tools round numbers silently.

A price like 1.108346 may be stored as 1.10835 or 1.1083 depending on the format settings. Profit figures lose their cents. Timestamps lose their milliseconds. Opened in a spreadsheet and saved again, a file can lose precision that was there in the original.

For tick data, the effect is pronounced. When bid and ask are rounded to four decimals, many ticks become identical, and spreads are distorted. A spread of 0.3 pips can show as 0.0 or 0.5.

Dates are another casualty. A spreadsheet may reinterpret a day-month format as month-day, silently changing data for the first twelve days of each month.

Good practice: keep raw files untouched, do analysis in code, and write intermediate results with full precision. Open spreadsheets only for viewing, not saving.

If a spreadsheet is necessary, check cell formats before importing, and compare a few values with the raw file afterward.

The errors don't announce themselves. They show up later as unexplained differences between two analyses of the same data.

Has a spreadsheet ever quietly changed a data set, and how was it caught?

[Amanhasnoname]

The date problem caught me properly once. I had been building a session study from a broker's export, opened the file in a spreadsheet to tidy up the column names and saved it. For the first twelve days of every month, day and month got swapped, and my results showed a strange cluster of activity on what looked like random dates. It took me two evenings to work out what had happened.

The rounding issue hit a friend of mine harder. He was measuring spread on gold from tick data, and after a save in a spreadsheet the prices lost a decimal place. His average spread looked almost a third lower than reality, and he based his position sizing for a new EA on that number.

These days I keep raw exports in a folder that is read-only, and every script starts by checking a few known values against the original file. If the number of decimals or the row count differs, the script stops before doing anything else. Boring, but it has saved me twice.