Importing CSV financial data in Mathematica

Question

I am trying to import a CSV file in Mathematica 8 with the aim of charting it with the TradingChart command. This is what I have done :

Take[Import["EURUSD_2004.csv"] ] // TableForm
(*
{
 {"DATE", "LOW", "MAX", "VOLUME", "OPEN", "CLOSE"},
 {"1998.01.05", 1.0815, 1.0985, 0, 1.0834, 1.0834},
 {"1998.01.06", 1.0795, 1.0875, 0, 1.08, 1.08},
 {"1998.01.07", 1.0778, 1.0865, 0, 1.0842, 1.0842},
 {"1998.01.08", 1.0792, 1.088, 0, 1.0844, 1.0844},
 {"1998.01.09", 1.0823, 1.0926, 0, 1.0829, 1.0829},
 {"1998.01.12", 1.0808, 1.09, 0, 1.0835, 1.0835},
 {"1998.01.13", 1.083, 1.09, 0, 1.0858, 1.0858},
 {"1998.01.14", 1.0812, 1.0859, 0, 1.0848, 1.0848},
 {"1998.01.15", 1.0785, 1.0872, 0, 1.0789, 1.0789},
 {"1998.01.16", 1.0787, 1.0838, 0, 1.0788, 1.0788},
 {"1998.01.19", 1.0739, 1.0812, 0, 1.0772, 1.0772},
 {"1998.01.20", 1.0733, 1.0779, 0, 1.075, 1.075},
 {"1998.01.21", 1.0755, 1.0852, 0, 1.0849, 1.0849},
 {"1998.01.22", 1.0849, 1.0962, 0, 1.0921, 1.0921},
 {"1998.01.23", 1.0919, 1.1105, 0, 1.1088, 1.1088},
 {"1998.01.26", 1.0919, 1.1203, 0, 1.0983, 1.0983}
} *)

TradingChart[EURUSD]
TradingChart::ldata: <<original data>> is not a valid dataset or list of datasets. >>

So the dataset is not in a valid form. Any hint on how to fix this ?

Answer 1

The data can be conveniently imported like this:

data = Import["data.csv", "HeaderLines" -> 1, "DateStringFormat" -> {"Year", "Month", "Day"}]

The "HeaderLines" option will ignore the first line. The "DateStringFormat" option makes sure that the dates are interpreted correctly. This also slows down import, but it won't matter if your file is this small.

The data is still not in the correct format though. The TradingChart documentation page tells us that we need to have it in the {date, {open, high, low, close, volume}} format. So let's do this:

formattedData = 
  Cases[data, {date_, low_, high_, volume_, open_, close_} :> 
              {date, {open, high, low, close, volume}}];

I believe this way of working with the data is more intuitive than using numerical indexes to access the columns. There are even ways to refer to columns by their names in the CSV file. See these two posts on the topic:

• Data Table Manipulation in Mathematica
• Data Table Manipulation in Mathematica: Step 2

Now you should be able to do TradingChart[formattedData]. Unfortunately this gives me a pink error box unless I plug in some random values for volume, but that is a separate issue ...

---

I assumed the CSV file looks like this:

DATE,LOW,MAX,VOLUME,OPEN,CLOSE
1998.01.05,1.0815,1.0985,0,1.0834,1.0834
1998.01.06,1.0795,1.0875,0,1.08,1.08
1998.01.07,1.0778,1.0865,0,1.0842,1.0842
1998.01.08,1.0792,1.088,0,1.0844,1.0844
1998.01.09,1.0823,1.0926,0,1.0829,1.0829
1998.01.12,1.0808,1.09,0,1.0835,1.0835
1998.01.13,1.083,1.09,0,1.0858,1.0858
1998.01.14,1.0812,1.0859,0,1.0848,1.0848
1998.01.15,1.0785,1.0872,0,1.0789,1.0789
1998.01.16,1.0787,1.0838,0,1.0788,1.0788
1998.01.19,1.0739,1.0812,0,1.0772,1.0772
1998.01.20,1.0733,1.0779,0,1.075,1.075
1998.01.21,1.0755,1.0852,0,1.0849,1.0849
1998.01.22,1.0849,1.0962,0,1.0921,1.0921
1998.01.23,1.0919,1.1105,0,1.1088,1.1088
1998.01.26,1.0919,1.1203,0,1.0983,1.0983