Added a button to normalize the date.

[shaperilio]

This will force the parsed date to be output as "YYYY-MM-DD", a format that YNAB is guaranteed to accept.

This was tested with real Venmo transactions, which were otherwise unimportable.

[aniav]

Hey @shaperilio what's the date format you get from Venmo? Would you mind sharing a few anonymised statement lines?

The code was previously always using moment(cell_value) to produce a Date object and I removed it long ago when YNAB introduced a date format selector in their settings. Also because parsing the date this way was producing varied results, as 2012-07-06 can either mean 6th of July or 7th of June, depending on part of the world you're in.

Date will parse only ISO formatted dates as @denkristoffer mentioned, so I'm thinking maybe we could make the code use some formatting library to try and properly guess the date format and produce the outcome in the YNAB valid format?

Also sorry you had to wait for a response for so long 🙏

[shaperilio]

Hello @aniav; no worries on the wait.

This is what I got from Venmo this morning:

Username,ID,Datetime,Type,Status,Note,From,To,Amount (total),Amount (fee),Funding Source,Destination,Beginning Balance,Ending Balance,Statement Period Venmo Fees,Terminal Location,Year to Date Venmo Fees,Disclaimer
my_user,,,,,,,,,,,,$520.81,,,,,
,3154016250804306394,2020-11-30T17:07:16,Charge,Complete,My Note,My Name,Payee,+ $45.00,,,Venmo balance,,,,Venmo,,
,3155147014426067350,2020-12-02T06:33:54,Standard Transfer,Issued,,,,"- $2,000.00",,,My Bank *9999,,,,Venmo,,
,3157241580838977867,2020-12-05T03:55:25,Payment,Complete,My Note,My Name,Payee,- $40.00,,Venmo balance,,,,,Venmo,,

It is unrecognizable by YNAB, as we know.

After running through your tool, YNAB does not recognize the date (which is taken directly from the CSV, e.g. the first one above is 2020-11-30T17:07:16):

Forgive my crude code for the Javscript universe is a world of confusion to me. To @denkristoffer's point, if you don't select the "Datetime" column and choose to normalize the date, you'll get "NaN-NaN-NaN", which I certainly find sufficient to indicate the user made a mistake (in part because I don't have the capacity to do anything more elegant).

Date parsing is always a nightmare, but something must have changed in the way Venmo outputs things (or the way YNAB imports things) and this works today (and has since I made the modification). We will likely have to do something again next time something changes. I don't think we need to worry about nnnn-nn-nn being confused as month-day or day-month, nor do we need to check the format of the date in Venmo's output. Perhaps a better way to do this is to simply throw away the string beyond the "T"; that way we are not interpreting the date in any way, and if Venmo changes the format, it may be more robust against mangling the date.

When I import what my version outputs with "normalized date" turned on, YNAB does not let me choose the date format. Examining the screenshot carefully, you can see that if you use hyphen as a separator, the only possibility is YYYY-MM-DD (however, YYYY/DD/MM and YYYY/MM/DD both exist). This lucky coincidence means we don't really need to communicate the format to the user.

Curiously, YNAB has a checkbox to "Adjust to local time", despite presumably not accepting any formats with the time included. Checking this box sets all my dates back a day. I think Venmo is outputting UTC.

Of note is that the thousands separator is a comma, and that transaction amount appears in quotes. Venmo is only available in the United States; do we have to worry about any internationalization?

If we know the Venmo output format is universally the same for all customers, there shouldn't be a "normalize" toggle at all.