Reading Calendar Dates into Mathematica from CSV files

(c) Weber & Partner, financial technology,
www.weber-und-partner.com

Reading simple CSV files

Comma separated data or CSV files are still the most common way to export data from trading systems and other applications in finance for analysis and prototyping. The import of basic CSV data into Mathematica is simple and fast with the ReadList command together with setting some options for ReadList like WordSeparators and RecordLists. In almost any case it is usefull to use Word as the data type in ReadList and let Mathematica do the conversion to numbers.

SetDirectory["O:\\resources\\ReadingDate"]

!! example1.dat

data  =  
      ReadList["Example1.dat", Word,  WordSeparators ->  ",", 
          RecordLists -> True]  //  ToExpression

Reading calendar dates into Mathematica

In Mathematica a date is usally represented as a list of three integers like the first three integers of the build in function Date. This format is also used in the few calendar functions which ship with Mathematica (see the standard package Miscellaneous`Calendar`)

Date[]

The above approach to read in data is not sufficient to make calendar dates available within Mathematica. To achieve this we have to tell Mathematica how a calendar date looks like in the data which will be read.

!! Example2.dat

The parts of the calendar dates are separated through slashes. We assume that each calendar date in the whole data set has the same structure. Based on this assumption we define a simple conversion function. In this case it is only necessary to do some replacements within the string and reverse it after it is converted to a list of three integers.

$DateTemplate  =  "*/*/*";
ToDateExpression[s_String /; StringMatchQ[s, $DateTemplate]] := 
        Reverse[ToExpression["{" <> 
              StringReplace[
                s, {"/" -> ","}] <> "}"]];

This function is only applied if the string which is passed to the function maches the date template. To simplify the use of this function a second definition for all other cases is provided and the function is made Listable, so that it will be applied to each element of a list.

ToDateExpression[x_] := x;
SetAttributes[ToDateExpression, Listable]

Based on these definitions one can proceed as before.

data  =  ReadList["Example2.dat", Word, 
            WordSeparators ->  ",", 
            RecordLists -> True]  //  ToDateExpression  //  
        ToExpression

We found this to be very handy and fast for reading in CSV files with calendar dates.

Some additional ideas

Practical experience shows that the above function is used very often. On Microsoft Windows the calendar format is part of the system setting, so the calendar dates in  CSV files which are generated on Microsoft Windows machine look very often the same. Therefore it makes sense to put the above function into the init.m file which is read from Mathematica at start up. In this case it might even make sense to go one step further. The above definition is nothing more then a special case of the ToExpression command, so we extend this Mathematica command accordingly.

$DateTemplate  =  "*/*/*";
$DateTemplate::usage = "$DateTemplate is used from the extended \
definition of ToExpression and describes the separation structure for \
calendar dates on the system";

Unprotect[ToExpression];
ToExpression[s_String /; StringMatchQ[s, $DateTemplate]] := 
      Reverse[ToExpression["{" <> 
            StringReplace[
              s, {"/" -> ","}] <> "}"]]
Protect[ToExpression];

\(\(ToExpression::usage = 
        ToExpression::usage <> "

If a string matching the pattern " <> $DateTemplate <> \ " (defined through $DateTemplate) is provided to ToExpression, it assumes a date of the form "dd/mm/yyyy"and returns a list of integer representing a date in Mathematica. (Defined in the init.m file)";\)\)

SetOptions[ReadList, WordSeparators ->  ",", 
        RecordLists -> True];

data  =  
      ReadList["Example2.dat", Word]  // ToExpression

The back side of this extension to Mathematica is, that you get used to them and take them for granted. Currently they will break if your system setting is changing or you update Mathematica.

Print["ToExpression is extended to handle calendar dates. See \
?ToExpression"]

?$DateTemplate

?? ToExpression