Skip to content
This repository has been archived by the owner on Mar 1, 2021. It is now read-only.

extract Ally operations data #23

Closed
chadwhitacre opened this issue Apr 12, 2016 · 27 comments
Closed

extract Ally operations data #23

chadwhitacre opened this issue Apr 12, 2016 · 27 comments

Comments

@chadwhitacre
Copy link
Contributor

Afaict we only have PDFs and we should at least have that data in CSV.

@chadwhitacre
Copy link
Contributor Author

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

@chadwhitacre
Copy link
Contributor Author

$ 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
$

@chadwhitacre
Copy link
Contributor Author

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

@chadwhitacre
Copy link
Contributor Author

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

@chadwhitacre
Copy link
Contributor Author

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

@chadwhitacre
Copy link
Contributor Author

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).

@chadwhitacre
Copy link
Contributor Author

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

@chadwhitacre
Copy link
Contributor Author

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)

@chadwhitacre
Copy link
Contributor Author

the CSV we apparently got directly from Ally

Scratch that. Directly from @kaguillera. ;-)

@chadwhitacre
Copy link
Contributor Author

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

@chadwhitacre
Copy link
Contributor Author

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

@kaguillera
Copy link
Contributor

Those were had done when we started on organising the finances

@kaguillera
Copy link
Contributor

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

@kaguillera
Copy link
Contributor

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

@chadwhitacre
Copy link
Contributor Author

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.

@chadwhitacre
Copy link
Contributor Author

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.

@chadwhitacre
Copy link
Contributor Author

Now to get dirty with some XML ...

@chadwhitacre
Copy link
Contributor Author

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.

@kaguillera
Copy link
Contributor

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.

@chadwhitacre
Copy link
Contributor Author

@chadwhitacre
Copy link
Contributor Author

(Well, a start, anyway.)

@chadwhitacre
Copy link
Contributor Author

  • 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

@chadwhitacre
Copy link
Contributor Author

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

@chadwhitacre
Copy link
Contributor Author

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

@chadwhitacre
Copy link
Contributor Author

Buh-lam.

@chadwhitacre
Copy link
Contributor Author

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

@chadwhitacre chadwhitacre changed the title extract Ally data extract Ally operations data Apr 13, 2016
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants