I run two weekly pinball tournaments here in Traverse City (https://tcpinball.org/) which we submit to the IFPA for global rankings.
The IFPA charges a $1 per player fee to be “sanctioned”. This fee is generously sponsored by each location that we run tournaments at. Thinking it’d be easy enough to pull back the player counts later on, I put the fee payments on my credit card and figured I’d get reimbursed later on.
This is what the stats page shows on IFPA, which is not useful to me, but it shows I’ve paid $568 as of this date in fees. I need to know how much each location owes me.
I spent probably 4 hours yesterday digging through the API docs for matchplay and the IFPA directly trying to get a count of players by location.
I ended up writing some code which didn’t quite solve it either. The list of tournaments that it gives back doesn’t include the player count, meaning I have to make another API call to get the player details for each tournament. I got rate-limited almost immediately just testing it.
While the data I need might not be accessible via the APIs, it’s right here on my TD page in a nice little table. Queue another hour of trying to parse the HTML with several different tools.
The brain blast came as I was eating dinner. Stop trying to parse it and just paste it into a spreadsheet. Works immediately. Save as a CSV and import to a sqlite db.
sqlite> .mode csv
sqlite> .import ifpa.csv ifpa
sqlite> select sum(players) from ifpa where tournament like '%coin slot%';
436
sqlite> select sum(players) from ifpa where tournament like '%right brain%';
148
I could’ve figured out how to match this up with a calculated column in the spreadsheet but the important feature there was just parsing the HTML from my clipboard.
Major props to sqlite yet again!
Leave a Reply