Script for Bank of Canada Prices

[Ben L]

I wrote a python script to get the daily USD/CAD current exchange rate from the Bank of Canada and output it into a .beancount file that can be include in my main file. I need this to properly report transactions to the CRA using the CAD value at the date of the transaction and maybe this will be helpful to other people.

The first time you run the script, it will get all the data starting from 2021-01-01. Subsequently, it will just query for new exchange rates since the last run and append them to the file. So if you manually edit a price, it will no get overwritten when you the script again.

import requests, json, os.path
import datetime

priceFilename = "BofC_CADprices.beancount"

if os.path.exists(priceFilename):
    priceFile = open(priceFilename, "r+")
    currentPrices = priceFile.readlines()

    last_line = currentPrices[-1:][0].split(' ')[0]

    last_date = datetime.datetime.strptime(last_line, '%Y-%m-%d').date()
    next_date = last_date + datetime.timedelta(days=1)
else:
    priceFile = open(priceFilename, "w")
    next_date = datetime.datetime.strptime('2021-01-01', '%Y-%m-%d').date()
end_date = datetime.date.today() - datetime.timedelta(days=1)

# Note that output format of dates should be %Y-%m-%d. Default is this, so it
# works, but maybe should use strptime to explicity set format
url = requests.get("https://www.bankofcanada.ca/valet/observations/FXUSDCAD/json?start_date={}&end_date={}".format(next_date,end_date))
data = json.loads(url.text)
rates = data['observations']

for r in rates:
    priceFile.write('{} price USD {} CAD;\n'.format(r['d'],r['FXUSDCAD']['v']))
priceFile.close()

[Chris Berkhout]

I've added support to pricehist (https://gitlab.com/chrisberkhout/pricehist) for Bank of Canada daily exchange rates.

For example:

$ pricehist fetch bankofcanada USD/CAD -s 2021-01-04 -e 2021-01-07 -o beancount

2021-01-04 price USD 1.2751 CAD
2021-01-05 price USD 1.2707 CAD
2021-01-06 price USD 1.2685 CAD
2021-01-07 price USD 1.2707 CAD

You can refetch everything to update, but if instead you want to fetch just new prices and append:

$ last=$(tail -1 prices.beancount | cut -d' ' -f1)

$ pricehist fetch bankofcanada USD/CAD -sx $last -o beancount >> prices.beancount

To see supported currency pairs:

$ pricehist source bankofcanada --symbols

AUD/CAD    Australian dollar to Canadian dollar daily exchange rate
BRL/CAD    Brazilian real to Canadian dollar daily exchange rate
CAD/AUD    Canadian dollar to Australian dollar daily exchange rate
CAD/BRL    Canadian dollar to Brazilian real daily exchange rate
...

It's available in version 1.3.0.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/a9fa28f2-2132-4dba-9c91-ad92ce8760dan%40googlegroups.com.