Now that Monzo Plus is out, i thought i'd share how i've set up my personal budgeting dashboards using the Google Sheets export that i showed off in this tweet: https://twitter.com/arussellsaw/status/1283876210750230530?s=20
A quick note on cost, bigquery isn't free, but even with my Monzo transaction history, which i've had since 2016 (although the google sheets only goes as far as the prepaid->current account migration) the volume of data here is still so low that bigquery doesn't even appear on my GCP billing, as we're well below the 1tb billing threshold. That being said if you somehow misconfigure things, it's possible that you could incur billing costs, so it's worth being wary of that.
The sheets export automatically creates and updates a google sheet with each transaction, and luckily google BigQuery supports using a google sheet as an external table, so we can just query the sheet directly, and have it updated as new rows are added to the sheet.
First we create a new bigquery table that looks like this, with the link to your monzo transactions sheet:
don't forget to add the 'Monzo Transactions' sheet range, and to skip the first header row in the advanced options!
and use this here as your schema:
transaction_id:STRING,
date:STRING,
time:STRING,
type:STRING,
name:STRING,
emoji:STRING,
category:STRING,
amount:NUMERIC,
currency:STRING,
local_amount:NUMERIC,
local_currency:STRING,
notes:STRING,
address:STRING,
receipt:STRING,
description:STRING,
category_split:STRING
Now we have a table, we should add a running balance to it to make building graphs a bit easier. In order to do this we'll add a scheduled query that sums the amount of all transactions sequentially, and attaches it as an additional column. We'll also reformat the timestamp to be a little more SQL friendly.
I have this query set up to run hourly, with the output being sent into a new table called monzo_transactions_hourly
WITH base AS (
SELECT
*
FROM
`russellsaw.personal_data.monzo_transactions`
WHERE transaction_id IS NOT NULL
),
timestamped AS (
SELECT
*,
PARSE_TIMESTAMP("%d/%m/%Y %T", CONCAT(date, " ", time)) as timestamp
FROM
base
),
balanced AS (
SELECT
*,
SUM(amount) OVER tx_date as balance
FROM
timestamped
WINDOW tx_date AS (ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
)
SELECT * FROM balancedNow that we have a table with an hourly balance we can start to build a dashboard! if you don't have a grafana instance already, head to https://grafana.com/grafana/download to get started, i recommend docker but it does make installing plugins harder, and you'll need to do that for the next step:
Next you'll want to install this extension here: https://grafana.com/grafana/plugins/doitintl-bigquery-datasource this is a datasource plugin for bigquery that allows us to directly query bigquery and build panels in grafana, super cool.
You'll need to create a service account and keyfile, i'd recommend creating a new IAM user that is only allowed to query bigquery, and then using that as the credentials for grafana, to prevent a compromise of your grafana instance leading to a compromise of your entire GCP account.
If you import the dashboard JSON below you should get a version of my dashboard, but the tables in the query will all point to my personal project, so you'll want to update the queries to use your table name. also some of the panels have specific references to my pots, so you'll want to make some edits for your own data.
And that should be done! if you have any issues @ me on twitter @arussellsaw and i'll see if i can help.