I’m working on setting up an “integration” of sorts between data from Simple bank and the data I have in my Tiller spreadsheet. The end goal is to update transactions in the Transactions spreadsheet with data from Simple, namely the correct goals/expense for a transaction and tweak the date to the date of the transaction, not the date it was received.
In order to make sure transactions match, I’m matching on the following columns in the Transaction spreadsheet:
- Date
- Description
- Amount
- Account
Description, Amount, and Account should be easy enough, but Date is more tricky. For starters, the Date in the Transactions spreadsheet doesn’t always match what I see in Simple. When I download the JSON export of the data from Simple, I see several dates associated with the transaction. For example, here are the dates from Simple for a recent transaction:
"times": {
"when_recorded": 1597093882497, //Monday, August 10, 2020 9:11:22.497 PM GMT
"when_recorded_local": "2020-08-10 21:11:22.497",
"when_received": 1597274631000, // Wednesday, August 12, 2020 11:23:51 PM GMT
"last_modified": 1597274652373,
"last_txvia": 1597274652373
},
In the Transactions sheet, the Date column has simply 8/12/2020. This tells me the date in the Transactions sheet correlates to the when_received date in Simple.
What I don’t know, however, is the timezone of the Date column in Simple. Without knowing the timezone, I’m not sure exactly how to match on the date of the transaction. For example, a when_received value of August 13, 2020 01:23:51 AM GMT could have a Date column value of 8/13/2020 if the timezone is GMT, but would have a value of 8/12/2020 if the timezone is EDT (or perhaps PDT).
Are we able to determine the timezone of the Date column in the Transactions spreadsheet?