6
$\begingroup$

I am writing a parser for my banking statements. The structure of the document is a header that, among other things, contains the text

Account statement from dd.mm.yyyy to dd.mm.yyyy

and a table with records such as

Booking date, Value date, Item, Debit, Credit
02/05         02/06       stuff -XX EUR

Normally all is well. I just take the first occurrence of yyyy to work out the year, then parse the MM/DD and call DateObject[{y,m,d}]. However, when a statement comes in for December/January, I might get something more difficult, like

Account statement from 30.12.2017 to 31.01.2018

And of course, all entries in January are incorrectly parsed as Jan'17 instead of Jan'18.

What would be an elegant way to select the correct year? Performance is not a big deal, but to a limit. That is, if MMA needs to use a top-level function to cast a string to a date once or twice per document, that's fine. But if it needs to do that for every entry in the statement, that's probably too much.

To keep the scope narrow, I don't want to discuss string parsing any more, than in the context of using date functions to parse them, therefore I provide the following example:

date1 = "30.12.2017"
date2 = "31.01.2018"
date = "01/02"

The desired result is the ISO formatted string "2018-01-02".

To give an example of the possible irregularities in the data, one of the statements is of the form

date1 = "19.05.2017"
date2 = "31.05.2017"
date = "05/18"

As you see, off by a couple of days is not unheard of in banking.

$\endgroup$
6
  • $\begingroup$ If all the headers follow that pattern: StringCases["Account statement from 30.12.2017 to 31.01.2018", s : DatePattern[{"Day", "Month", "Year"}, "."] :> DateList[s]] to pick out the dates, and then check the years in both bracketing dates so you know what to apply on the strings like "02/06". $\endgroup$ Mar 20, 2018 at 14:03
  • $\begingroup$ @J.M. this was not my question, but I didn't think of DatePattern and was parsing strings more manually, that's good to know. $\endgroup$
    – LLlAMnYP
    Mar 20, 2018 at 14:12
  • $\begingroup$ @Kuba one moment, I'll provide an example. $\endgroup$
    – LLlAMnYP
    Mar 20, 2018 at 14:12
  • $\begingroup$ I was trying to answer "what would be an elegant way to select the correct year?": you add a special case for when the bracketing months are December and January, which you can now detect through the method in my first comment. $\endgroup$ Mar 20, 2018 at 14:15
  • 1
    $\begingroup$ Related: mathematica.stackexchange.com/questions/79241/… $\endgroup$ Mar 20, 2018 at 14:34

5 Answers 5

4
$\begingroup$

Update

There was an error in my last edit. Also, I've improved the speed a bit and packaged the code up as a function.

Here is an alternate implementation of your algorithm:

toDateList[date_String, {start_, end_}] := Module[{a1, a2, years, md, possible},
    (* adding seconds is faster than DatePlus *)
    a1 = AbsoluteTime[{start, {"Day", "Month", "Year"}}] - 432000;
    a2 = AbsoluteTime[{end, {"Day", "Month", "Year"}}] + 432000;
    years = DateValue[#, "Year"]& /@ {a1, a2};
    (* using Part is faster than DateValue *)
    md = DateList[{date, {"Month", "Day"}}][[{2, 3}]];
    possible = Prepend[md, #]& /@ years;
    Replace[
        Pick[possible, Between[{a1, a2}] /@ AbsoluteTime /@ possible],
        {
            {p_, ___} :> p,
            _ -> $Failed
        }
    ]
]

Some examples:

toDateList["01/02", {"30.12.2017", "31.01.2018"}] //RepeatedTiming
toDateList["05/18", {"19.05.2017", "31.05.2017"}] //RepeatedTiming
toDateList["12/31", {"01.01.2017", "02.01.2017"}] //RepeatedTiming

{0.0010, {2018, 1, 2}}

{0.0011, {2017, 5, 18}}

{0.0011, {2016, 12, 31}}

Note that using date functions like AbsoluteTime and DateList on strings will be slower than using string manipulation functions as you do in your answer.

$\endgroup$
5
  • $\begingroup$ Same, same, but different. Clean. I like this. +1 $\endgroup$
    – LLlAMnYP
    Mar 20, 2018 at 15:19
  • $\begingroup$ @Carl Woll: How do you motivate the magic 5 days inside DatePlus[]? $\endgroup$ Mar 23, 2018 at 9:11
  • $\begingroup$ @Ulrich, from the OP: "As you see, off by a couple of days is not unheard of in banking." Carl decided to add a margin of five days, which you can adjust if you think it is too wide. $\endgroup$ Mar 23, 2018 at 9:48
  • $\begingroup$ @UlrichNeumann as you can see, I implemented the very same margin of 5 days in my own solution too. $\endgroup$
    – LLlAMnYP
    Mar 23, 2018 at 9:53
  • $\begingroup$ @Carl, conversion to AbsoluteTime is unnecessary, your method works directly on DateObjects: d1d2={DateObject[{2017,12,30}]-Quantity[5,"Days"],DateObject[{2018,1,31}]+Quantity[5,"Days"]} then possible = Map[Prepend[md, #] & /* DateObject, years] finally: Select[possible, Between[d1d2]] $\endgroup$ Mar 23, 2018 at 11:35
3
+50
$\begingroup$

This solution is similar to @LLIAMnYP's but refined to work well with weird dates.

GetYear[d_, {st_, ed_}] := 
 With[{std = (st[[;; , 2 ;;]] - 1).{62, 2}, edd = (ed[[;; , 2 ;;]] - 1).{62, 2}, dd = (d - 1).{62, 2}}, 
  Range[st[[;; , 1]] - UnitStep[15 - std] + UnitStep[SawtoothWave[(std - 15)/743] - dd/743], 
   ed[[;; , 1]] + UnitStep[edd + 15 - 743] - UnitStep[dd/743 - SawtoothWave[(edd + 15)/743]]]
]

GetString[d_, {st_, ed_}] := 
 With[{f1 = FromDigits@Reverse@StringSplit[#, "."] &, 
   f2 = FromDigits@StringSplit[#, "/"] &, 
   pd = (If[StringLength@# == 1, "0" <> #, #] &)@*ToString},
  MapThread[(Function[{y}, ToString[y] <> "-" <> StringReplace[#2, "/" -> "-"]] /@ #1) &,
  {GetYear[f2@d, {f1 /@ st, f1 /@ ed}], d}]
  ]

GetString[d_String, {st_String, ed_String}] := GetString[{d}, {{st}, {ed}}]

Tests:

GetString["03/04", {"01.01.2016", "31.12.2016"}]
>{{"2016-03-04"}}

GetString[{"03/04","12/31"}, {{"01.01.2016", "01.01.2017"}, {"31.12.2016", "02.01.2017"}}]
>{{"2016-03-04"}, {"2016-12-31"}}

GetString[ConstantArray["12/31", 1000], Thread@ConstantArray[{"01.01.2016", "01.01.2017"}, 1000]][[1]] // AbsoluteTiming
>{0.094483, {"2015-12-31", "2016-12-31"}}

Seemingly good!

Comparing to previously mentioned methods, this method based solely on mathematical functions and basic string operations, so it's mostly Listable and works quite fast( approximately 6x faster than Edmund's answer).

In this piece of code, the tolerance is not fixed, but range from 4 days (around Feb-28) to 7 days. But as such things rarely happens and tolerance is quite arbitrary, I think this won't matter. Also, multiple results could be given by this code, as demonstrated in the last piece of test code.

The basic idea is to convert a date into a number by {mm,dd}.{62,2} then add a periodical boundary using SawtoothWave to deal with dates like Jan-01 or Dec-31.

Do this piece of code meet your need?

$\endgroup$
3
  • $\begingroup$ The same advice I gave to Edmund: use FromDigits[] instead of ToExpression[] for converting number strings to numbers. $\endgroup$ Mar 24, 2018 at 14:05
  • $\begingroup$ @J.M. Thanks for your advice! I would edit it now. $\endgroup$
    – Wjx
    Mar 25, 2018 at 1:43
  • $\begingroup$ Thanks for the input, I'll have a look tomorrow $\endgroup$
    – LLlAMnYP
    Mar 25, 2018 at 17:11
2
$\begingroup$

Presuming

  • the statement dates are in adjacent months
  • transactions can occur in the past (before starting statement date)
  • transactions cannot occur in the future (after ending statement date)

Then the only date that can be in a year different to the statement starting date is a date with a month of the statement ending date.

With

date1 = "30.12.2017";
date2 = "31.01.2018";
date = "01/02";

and

{d1, d2} = DateObject[{#, {"Day", "Month", "Year"}}, "Day"] & /@ {date1, date2}

then

DateObject@
 MapAt[Replace[Thread[{1, _} -> DateValue[{d2, d1}, "Year"]]], 1]@
  Map[FromDigits, StringSplit[date, "/"]][[{1, 1, 2}]]

Mathematica graphics

Also, with

date1 = "19.05.2017";
date2 = "31.05.2017";
date = "05/18";

and again

{d1, d2} = DateObject[{#, {"Day", "Month", "Year"}}, "Day"] & /@ {date1, date2}

then

DateObject@
 MapAt[Replace[Thread[{1, _} -> DateValue[{d2, d1}, "Year"]]], 1]@
  Map[FromDigits, StringSplit[date, "/"]][[{1, 1, 2}]]

Mathematica graphics

Hope this helps.

$\endgroup$
6
  • $\begingroup$ This does not seem to work on 10.2... new syntax rules in 11+? $\endgroup$
    – LLlAMnYP
    Mar 22, 2018 at 8:00
  • $\begingroup$ {d1, d2} = DateObject /@ {date1, date2} works just fine. +1 $\endgroup$
    – LLlAMnYP
    Mar 22, 2018 at 8:15
  • $\begingroup$ @LLIAMnYP Yes, I tend to fully specify my date conversions as computer locales can be different to mine. I am using 11.3 and see that you have used a 10.2 conversion that works well for you. $\endgroup$
    – Edmund
    Mar 22, 2018 at 8:52
  • 1
    $\begingroup$ It's safer to use FromDigits[] than ToExpression[]: FromDigits /@ StringSplit[date, "/"] $\endgroup$ Mar 22, 2018 at 11:34
  • 1
    $\begingroup$ The feasible example date1 = "01.01.2017";date2 = "02.01.2017" ;date = "12/31" fails with your code! $\endgroup$ Mar 23, 2018 at 9:17
1
$\begingroup$

Here's my dirty attempt at a solution.

Assuming I start at my example:

date1 = "30.12.2017"
date2 = "31.01.2018"
date = "01/02"

{year1, year2} = ToExpression[StringTake[#, -4]] & /@ {date1, date2};
{date1, date2} = 
  AbsoluteTime[StringSplit[#, "."] // ToExpression // Reverse] & /@
    {date1, date2} + 5*86400 {-1, 1}; (* plus-minus five days for the bank's imprecision*)
possible = Flatten[{#, ReadList[StringToStream[date], Number,
  RecordSeparators -> "/"]}] & /@ Range[year1 - 1, year2]
possible = Select[possible, date1 <= AbsoluteTime@# <= date2 &] // First
DateString[possible, "ISODate"]

Thank to Ulrich who noted that a date may actually not be in year1 or year2.

This works, but I've got a feeling I'm doing this badly wrong. I'm sure, MMA has got many built-ins that would simplify the solution... (although they may be slower)

Benchmarking
Wrapping all the proposed solutions in a

Block[{date1 = "30.12.2017",
       date2 = "31.01.2018",
       date = "01/02"},
      code
] // RepeatedTiming

gives

0.00369 (* Carl Woll *)
0.00068 (* Edmund *)
0.00023 (* LLlAMnYP *)
$\endgroup$
2
  • $\begingroup$ The feasible example date1 = "01.01.2017";date2 = "02.01.2017" ;date = "12/31" fails with your code ! $\endgroup$ Mar 23, 2018 at 9:19
  • $\begingroup$ @UlrichNeumann Very good edge case, thank you. I'll see what could be done. $\endgroup$
    – LLlAMnYP
    Mar 23, 2018 at 9:28
1
$\begingroup$

With the assumptiones date1<=date2, date<=date2 believing on a fast banking performance "date1-date smaller 5days" a possible solution , based on Carl Wolls approach, could be:

d1 = AbsoluteTime[{date1, {"Day", "Month", "Year"}}]  ;
d2 = AbsoluteTime[{date2, {"Day", "Month", "Year"}}] ;
years = Map[DateValue[#, "Year"] &, {d1 - 5*24 3600, d2}]; 
md = DateValue[{date, {"Month", "Day"}}, {"Month", "Day"}]  ;
possible = Map[AbsoluteTime[Prepend[md, #]] &, years] ;
Replace[ Select[possible,d1 - 5*24 3600 <= # <= d2 &] , {{p_, ___} :>DateString[p, "ISODate"], _ -> $Failed}]

The "edge case"

date1 = "01.01.2017"
date2 = "02.01.2017"
date = "12/31" 

evaluates to

(* {"2016-12-31"} *)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.