Totalling my expenses

I've been tracking everything I've acquired all year, and how much it cost. I even built a wee budgeting app on top of this feed last summer. This all worked fine until I was travelling a lot, and logging purchases in the local currency. I ignored it for a while, and could only budget for things I was buying in USD, however when it came to summarising all of this for the year-in-review I had to deal with it.

I searched for currency conversion APIs, and found which is not rate-limited and takes a date parameter for historical exchange rates. However, it only contains about 20 currencies, and didn't include Qatari Rial, which I need from my trip to Doha in March. There are various APIs which contain more currencies, but all the ones I found are rate limited and required API keys. They usually have other restrictions on the free tier, as well.

Initially I wrote a script that cycled through every single acquire post and fetched the exchange rate for that currency from currencylayer, one by one. I immediately exceeded 1000 request (per month!) rate limit, fetching all exchange rates for all individual purchases in a year.

Screenshot of email from currencylayer saying I'm approaching API limit

So then I wrote a script that cycled through all of my posts and aggregated the currencies I used for each day, by date. I then grabbed the exchange rates for these from in one request per day (rather than per post). is not rate limited, and this cut the number of requests down to about one third or less. Then only for rates that were missing from the response, I called rate-limted currencylayer to retrieve those, again in one request per day. This worked in bringing the number of requests down, but for anything more than a couple of months was still a request per day in the time period to the API, and took a while. Also, while I thought I wouldn't exceed the currencylayer rate limit given I have a finite amount of posts with currencies missing from (in this case ten days of QAR) in the whole year, this is not sustainable in the long term, as I don't know what other currencies I'll be spending over time. But the main problem is that it was really slow.

So, new plan. I moved all my code into a standalone script, which cycles through acquire posts between two dates and retrieves the currency codes used per day, then fetches the relevant exchange rates. Then dumps this to a JSON file. So, I ran that once.

Now my /summary code just consults this static JSON file. Since historical exchange rates don't change... this is all I need. For some reason, I'd like it to be more dynamic. I don't want to be storing random data about the world on my own server if I can fetch it from somewhere else. Oh well.

The code is here.

It still takes ages to run for a whole year, but at least I only have to do it once now.

If you have your own feed of posts with currencies, you can just modify `posts_between` to fetch them from your own system, and `get_rates` to read the cost from the post. I'll add that I have a couple of functions to wrangle official 3 digit currency codes from the messy inconsistent values of cost I have for my posts, including a local cache of all currency codes.

Next is to update my publishing endpoint to trigger fetching and storing the exchange rates for a new day when the first acquire post of that day/currency combination is made.

🏷 hacking php sloph

Post created with