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$
  • $\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$ – J. M. will be back soon Mar 20 '18 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 '18 at 14:12
  • $\begingroup$ @Kuba one moment, I'll provide an example. $\endgroup$ – LLlAMnYP Mar 20 '18 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$ – J. M. will be back soon Mar 20 '18 at 14:15
  • 1
    $\begingroup$ Related: mathematica.stackexchange.com/questions/79241/… $\endgroup$ – alancalvitti Mar 20 '18 at 14:34
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$
  • $\begingroup$ Same, same, but different. Clean. I like this. +1 $\endgroup$ – LLlAMnYP Mar 20 '18 at 15:19
  • $\begingroup$ @Carl Woll: How do you motivate the magic 5 days inside DatePlus[]? $\endgroup$ – Ulrich Neumann Mar 23 '18 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$ – J. M. will be back soon Mar 23 '18 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 '18 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$ – alancalvitti Mar 23 '18 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$
  • $\begingroup$ The same advice I gave to Edmund: use FromDigits[] instead of ToExpression[] for converting number strings to numbers. $\endgroup$ – J. M. will be back soon Mar 24 '18 at 14:05
  • $\begingroup$ @J.M. Thanks for your advice! I would edit it now. $\endgroup$ – Wjx Mar 25 '18 at 1:43
  • $\begingroup$ Thanks for the input, I'll have a look tomorrow $\endgroup$ – LLlAMnYP Mar 25 '18 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$
  • $\begingroup$ This does not seem to work on 10.2... new syntax rules in 11+? $\endgroup$ – LLlAMnYP Mar 22 '18 at 8:00
  • $\begingroup$ {d1, d2} = DateObject /@ {date1, date2} works just fine. +1 $\endgroup$ – LLlAMnYP Mar 22 '18 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 '18 at 8:52
  • 1
    $\begingroup$ It's safer to use FromDigits[] than ToExpression[]: FromDigits /@ StringSplit[date, "/"] $\endgroup$ – J. M. will be back soon Mar 22 '18 at 11:34
  • 1
    $\begingroup$ The feasible example date1 = "01.01.2017";date2 = "02.01.2017" ;date = "12/31" fails with your code! $\endgroup$ – Ulrich Neumann Mar 23 '18 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$
  • $\begingroup$ The feasible example date1 = "01.01.2017";date2 = "02.01.2017" ;date = "12/31" fails with your code ! $\endgroup$ – Ulrich Neumann Mar 23 '18 at 9:19
  • $\begingroup$ @UlrichNeumann Very good edge case, thank you. I'll see what could be done. $\endgroup$ – LLlAMnYP Mar 23 '18 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.