# Is it possible to import dates and times directly as AbsoluteTime and by pass DateLists?

Date and time calculations are quite slow so I am exploring ways to minimize the number of calculations performed. As an example:

msft = FinancialData["MSFT", "Jan 1, 2005"]
Timing[DateListPlot[msft]]
{0.253632, *picture*}


whereas

Timing[
msft1 = msft;
msft1[[All, 1]] = AbsoluteTime /@ msft[[All, 1]];
DateListPlot[msft1]
]
{0.214658, *picture*}


So it is faster to convert to AbsoluteTime and make a plot than to stick with date lists (as a side issue this can actually be done even faster with ListPlot or ListLinePlot and a tick function).

When wanting to work with date lists to select subsets of data you will typically choose a search criteria in which the absolute time of elements is tested against some condition. So this is another example where it is useful to already have the data in absolute time.

So the question is whether it is possible to import dates directly into Mathematica and have them as absolute times rather than date lists. My two specific examples would be FinancialData as per the example above -- can this be made to output absolute times via some configuration rather than having to convert the data post import? Also for Excel spreadsheets containing columns with dates (i.e. date strings as per Excel formats) and data. Can these be imported and converted to absolute times during the import process instead of converted to, and output as, date lists?

(If solutions to this problem means that Import and FinancialData internals take date lists and convert to absolute time then I don't really see that as a solution. I am seeking ways to go direct to absolute time.)

-
Mike, could you please supply an Excel file to test this with? –  Mr.Wizard Feb 11 '12 at 10:04
@Mr.Wizard I'll make one and add a link to my drop box. Probably not until tomorrow. –  Mike Honeychurch Feb 11 '12 at 21:00
@Mr.Wizard here is a sample Excel file dl.dropbox.com/u/31372535/test.xls –  Mike Honeychurch Feb 13 '12 at 0:19
Mike, could you please upload a smaller sheet? I am trying to work through this doing Trace[Import["test.xls"]] but it is too big and locks up the FrontEnd. –  Mr.Wizard Feb 14 '12 at 7:17

The data in the file test.xls are

03/Jan/2000   45.46
04/Jan/2000   43.92
05/Jan/2000   44.38
06/Jan/2000   42.9
07/Jan/2000   43.46
10/Jan/2000   43.78
11/Jan/2000   42.65
12/Jan/2000   41.26
13/Jan/2000   42.04
14/Jan/2000   43.78


An alternative approach is to exploit the fact that Office documents are zipped collections of XML files. So,

Step 1: rename the source file by adding .zip to the file name: test.xlsx.zip.

Step 2: Import the appropriate xml file in the zip file, extract the data elements and re-format:

 Cases[Import["C:\\ your directory \\test.xlsx.zip", {"ZIP", "xl\\worksheets\\sheet1.xml"}],
XMLElement["v", {}, {value_}] :> value, Infinity]
// Partition[#, 2] &


This gives:

{{"36528", "45.46"}, {"36529", "43.92"}, {"36530", "44.38"}, {"36531", "42.9"}, {"36532", "43.46"}, {"36535", "43.78"}, {"36536", "42.65"}, {"36537", "41.26"}, {"36538", "42.04"}, {"36539", "43.78"}}

where the first entry in each sublist is Excel's DATEVALUE (serial date number that counts the number of days from 1/1/1900).

Puzzle: I would expect that converting Excel's DATEVALUE to Mathematica's AbsoluteTime (number of seconds from 1/1/1900) would be as simple as multiplying the former by 24*60*60. But doing that with:

  excelDateValues = {"36528", "36529", "36530", "36531", "36532", "36535", "36536", "36537", "36538", "36539"}


and

  DateList /@ (24*60*60*ToExpression@excelDateValues)


gives

{{2000, 1, 5, 0, 0, 0.}, {2000, 1, 6, 0, 0, 0.}, {2000, 1, 7, 0, 0, 0.},
{2000, 1, 8, 0, 0, 0.}, {2000, 1, 9, 0, 0, 0.}, {2000, 1, 12,  0, 0, 0.},
{2000, 1, 13, 0, 0, 0.}, {2000, 1, 14, 0, 0, 0.}, {2000, 1, 15, 0, 0, 0.},
{2000, 1, 16, 0, 0, 0.}}


which is off by two days. Hopefully, there is a less naive approach to the get the right conversion factor to go from excel Datevalues to Mma AbsoluteTime so that a modified version of Cases[] above gives the desired result.

Puzzle resolved: Thanks to Mr.Wizard's reference, the historical background to the two-day discrepancy is explained beautifully in Joel Spolsky's great story . So, unless your data does contain dates going back early 1900's for most cases just subtracting 2 from final output dates should be ok. But ... things can get more complicated considering possible excel date system settings and varying defaults accross OSs. (see XL 1900 and 1904 date systems)

EDIT: Import uses the filename extension if no format is provided as the second argument. For zip files it returns the filenames in the zipped archive. For the example case

   Import["C:\\ your directory \\test.xlsx.zip"]


returns

 {"[Content_Types].xml", "_rels\\.rels","xl\\_rels\\workbook.xml.rels", "xl\\workbook.xml", "xl\\styles.xml",  "xl\\worksheets\\sheet1.xml", "xl\\theme\\theme1.xml", "customXml\\item1.xml", "customXml\\_rels\\item1.xml.rels", "customXml\\_rels\\item2.xml.rels", "docProps\\app.xml", "customXml\\itemProps2.xml", "customXml\\item2.xml", "customXml\\itemProps1.xml", "docProps\\core.xml"}

-
@Mr.Wizard, thank you again on two counts. I was aware that replace tricks would only work after importing but now I see the wisdom of ToDate=Identity. Thank you also for the link - wonderful answer. –  kguler Feb 15 '12 at 10:38
@Mr.Wizard, just deleted the first part. Thanks so much again. –  kguler Feb 15 '12 at 11:14
pretty cool. How did you learn about the {"ZIP", "xl/worksheets/sheet1.xml"} (Mac syntax) bit? –  Mike Honeychurch Feb 15 '12 at 22:28
@Mike, thank you for the accept. I had used the zip import in MMa in the past; and everytime it involved many trials and errors before I get the right syntax. This case no exception. The trick about renaming office docs with a .zip extension to see the uncompressed xml files has been around since xlsx ... docx format were introduced. –  kguler Feb 15 '12 at 23:46
but surely you didn't guess "xl/worksheets/sheet1.xml"? where did that come from? –  Mike Honeychurch Feb 15 '12 at 23:52

Unfortunately, according to the documentation, Import automatically converts things that look like dates in an Excel spreadsheet to DateList format.

I tried messing about with alternative ways of importing such as realrawdata = ("Data" /. Import["Data for graphs.xlsx", "Rules"])[[1]], but no luck.

It might be possible to develop your own import converter, but this seems like an awful lot of work.

-
For the spreadsheet part of the question I think what is probably required is a modification/revision to SystemConvertExcelDumpGetDataSheet but I can't figure out how to see the code to adapt a new converter based on it. –  Mike Honeychurch Jan 23 '12 at 19:32

I have not yet figured out how to change or configure the J/Link modules that do the actual translation, but there is a wasteful (from your perspective) application of ToDate which we can bypass, thereby returning dates in AbsoluteTime format and saving a bit of time during the Import. (specifying the format is a bit faster than letting Mathematica detect it.)

Block[{ToDate = Identity},
Import["test.xls", "XLS"]
]


The low level conversion function appears to be inside:

\SystemFiles\Converters\Java\Convert.jar\System\Convert\ExcellDump.class

I don't know how to decompile a .class file much less how to write one, but it appears to be quite simple.

-
thanks for your efforts. I think what is basically needed is an option for Excel import that allows you to specify the date format. Working with DateList is so horribly slow the default should be AbsoluteTime (IMO). This is also painfully slow but delivers less pain :) –  Mike Honeychurch Feb 14 '12 at 10:03
@Mike I am looking for any such option but without result. You can trace the JLink function to see any possibilities for yourself: SystemConvertExcelDumpread["C:\\test.xls", False] // Trace I think this is going to take editing the ExcellDump.class file; who here knows Java? –  Mr.Wizard Feb 14 '12 at 10:11