extract Ally operations data about finances HOT 27 CLOSED

Those were had done when we started on organising the finances

from finances.

I find an ally-operations.csv file with data from 2013-06-14 through 2014-03-10.

from finances.

$ find 201* -name ally.pdf
2013/07/ally.pdf
2013/08/ally.pdf
2013/09/ally.pdf
2013/10/ally.pdf
2013/11/ally.pdf
2013/12/ally.pdf
2014/01/ally.pdf
2014/02/ally.pdf
2014/03/ally.pdf
2014/04/ally.pdf
2014/05/ally.pdf
2014/06/ally.pdf
2014/07/ally.pdf
2014/08/ally.pdf
2014/09/ally.pdf
2014/10/ally.pdf
2014/11/ally.pdf
2014/12/ally.pdf
2015/01/ally.pdf
2015/02/ally.pdf
2015/03/ally.pdf
2015/04/ally.pdf
2015/05/ally.pdf
$

from finances.

2013/07/ally.pdf does include the 2013-06 transactions.

from finances.

Yeah, quick check of the rest looks like the CSV data we do have lines up with the PDFs we have. But we appear to be missing CSV data for 14 months. 😞

2014/04/ally.pdf
2014/05/ally.pdf
2014/06/ally.pdf
2014/07/ally.pdf
2014/08/ally.pdf
2014/09/ally.pdf
2014/10/ally.pdf
2014/11/ally.pdf
2014/12/ally.pdf
2015/01/ally.pdf
2015/02/ally.pdf
2015/03/ally.pdf
2015/04/ally.pdf
2015/05/ally.pdf

from finances.

PDF spot check shows that we do have activity in the later months (as expected).

from finances.

Alright, so ... I found a tool called TET and was able to extract PDF data to an XML format in https://github.com/gratipay/logs/commit/68745f8dce45da7e7c58f70e2f9f61fcd3b4ee43. I'm hoping to be able to script the extraction from there. Since we have some PDF/CSV overlap, we can use that to validate the script (target output is the CSV we apparently got directly from Ally).

from finances.

@kaguillera Do you remember if you hand-produced the ally-operations.csv file? The commit is https://github.com/gratipay/logs/commit/e01334ea2d32ddb3e0832b868a296960257b26d0.

from finances.

Looks like it:

I'm going to give @kaguillera access to the logs repo so he can convert the Ally and Citizens statements from PDF to CSV.

gratipay/inside.gratipay.com#308 (comment)

from finances.

the CSV we apparently got directly from Ally

Scratch that. Directly from @kaguillera. ;-)

from finances.

Alright, so let's see if we can spare @kaguillera the trouble and script the conversion of the remainder of the files ...

from finances.

Next step is to take the misc/308/ally-operations.csv data file and split it up into statements/year/month/ally-operations.csv.

from finances.

at the time I don't that we considered using a script to do the extraction due to the fact that they were PDF files

from finances.

Not sure why those other months are missing though. I was under the assumption that I had completed all the conversions unless I did not commit and push all my data entries. Let me check

from finances.

Not sure why those other months are missing though

I think we just didn't finish the conversion. We started trying to reconcile 2012-06 instead.

from finances.

Next step is to take the misc/308/ally-operations.csv data file and split it up into statements/year/month/ally-operations.csv.

Done at https://github.com/gratipay/logs/pull/11#issuecomment-209081676.

from finances.

Now to get dirty with some XML ...

from finances.

Looking at the TETML structure of these Ally statements, I am seeing "Activity" inside a <Para> signaling the start of a transaction block, ending with a sibling <Table> that contains the amounts the go with the text descriptions in the Paras.

from finances.

Don't have it so you are right...

I think we just didn't finish the conversion. We started trying to reconcile 2012-06 instead.

from finances.

Conversion script for Ally TETML to CSV in https://github.com/gratipay/logs/pull/11/commits/441186611901ad7e5d7d9a3108dc951f0a054430.

from finances.

(Well, a start, anyway.)

from finances.

• split PDFs across month boundaries (statements are offset but CSVs shouldn't be)
• verify output against @kaguillera's hand-made CSVs as well as source PDFs
• extract ally-escrow.csv from PDFs in addition to -operations

from finances.

Here's the extraction script for those following along at home (without access to the logs repo). BeautifulSoup! :-)

from finances.

P.S. I'm hot on this because I need this data in order to file my taxes. 😩

from finances.

Buh-lam.

from finances.

Ally operations CSVs cleaned up in https://github.com/gratipay/logs/pull/11/commits/53e14b7d73719e2bce1e643238fe99a91ee497db and extended in https://github.com/gratipay/logs/pull/11/commits/219f50449466beb7045929b67fdf2744f045c0cd.

from finances.

Bumping on escrow extraction because I don't need that for taxes.

from finances.