2e:null 2f:["$","title",null,{"children":"Working with private Splitgraph data in Observable | Peter Neumark | Observable"}] 34:T52d,How we spend money is often determined by convenience and force of habit rather than what makes financial sense. I've long wondered just how rational of a consumer I am. In this notebook I decided to tackle some personal finance questions that were bouncing around in my head: * How much are we spending on the family dog? Is it more or less than what we spend on basic needs like groceries? * How frequently do I make an impulse purchase at the nearby hipster coffee stand? * How much does our spending vary week to week? ## Importing my account statement Most banks allow exporting card payment history as a CSV file. Our family bank account is managed by Revolut [which is no exception](https://www.revolut.com/en-US/help/my-accounts/managing-my-account/viewing-my-account-statements) fortunately. With the handy [Splitgraph CSV uploader](https://www.splitgraph.com/upload) our account statement was queryable within seconds. I made sure to set repository sharing `Private` so I wasn't sharing my account statement with the entire world. I also created a sample table by selecting a few transactions from the entire import. It has enough rows that we can plot it, but not enough to prevent me from making it public. This way, if you want to follow along but don't have a CSV bank statement of your own at hand, you can!35:T503,datasource === "Public" ? md`This notebook is currently using the [Splitgraph DDN functions](https://observablehq.com/@splitgraph/ddn) to query the [public sample table](https://www.splitgraph.com/neumark/revolut-sample) over HTTP. This requires no extra setup, but it means only a fraction of the transactions of the full account statement are available.` : md`This notebook is currently querying Splitgraph as a [PostgreSQL database](https://observablehq.com/@observablehq/databases), providing access my full account history. Please note that this only works for _private Observable notebooks_. Public notebooks will display \`Error: Databases are only available in private notebooks\`. You must also set up the database connection as detailed below. ### DB connection setup: Splitgraph On the _SQL Credentials_ page, create a new set of credentials for Observable.

### DB connection setup: Observable Create a [new Database connection in Observable](https://observablehq.com/settings/databases?new). Only the _User_ and _Password_ fields should deviate from the screenshot below:

`36:T5b0,// If you're using your own bank account statement, you probably need to add some businesses to the patterns below. categories = ({ "groceries": [ new RegExp("Spar "), new RegExp("^Cba Pri"), new RegExp("^Penny "), new RegExp("Lidl .*"), new RegExp("Tesco"), new RegExp("Auchan.*"), new RegExp("Aldi .*"), "Family Market", "Sayed Parviz Kft", new RegExp("szatyorbolt"), new RegExp("E'lelmiszer") ], "pets": [ "Tutivet Allatorvosi", new RegExp("petissimo.hu"), new RegExp("Zooplus Ag"), new RegExp("Fressnapf "), new RegExp("www\\.nrdogs\\.hu") ], "furniture": [ new RegExp("Ikea"), new RegExp("Mo:max"), new RegExp("Diego"), new RegExp("^Jysk"), ], "coffee": [ "Harminckettesek Tere", "Koronaherceg Kft.", "Lipo'ti Pe'kse'g E's K", new RegExp("^Rauf "), new RegExp("^Sumup .*mtrm Roast$"), "Muterem Kavezo", new RegExp("outside The Bo$"), new RegExp("^Starbucks"), "Addicted To Caffeine", new RegExp("Kavezo"), "Budai 3" ], "pharmacy": [ "Benu Hajnal Gyo'gyszer", "Harom Sas Bt.", new RegExp("Gyogyszertar"), "Fehe'rva'r Gyo'gyszert" ], "household": [ new RegExp("Rossmann "), new RegExp("^Dm ") ], "restaurant": [ "Padthai Wokbar", "Jk-Gold Kft", new RegExp("Delivery Hero Hungary"), "Hummus Bar", new RegExp("[p|P]izza"), new RegExp("netpincer") ] })37:T4ac,addTooltips(Plot.plot({ y: { grid: true }, marks: [ Plot.line( await query(` WITH weekly_transactions AS (SELECT t.week_start, u.* FROM ( SELECT * FROM generate_series( '2018-01-01'::DATE, '2022-06-01'::DATE, '1 week' ) AS week_start ) t JOIN ( SELECT "Description" AS business, "Started Date" as ts, -- Note: the CAST is needed so the total_spent column contains numbers not strings -- Multiply by -1 to make the amounts positive since spending shows up as a negative -- value in the account statement. CAST("Amount" * -1 AS DOUBLE PRECISION) as amount FROM ${tableName} WHERE "Type" = 'CARD_PAYMENT' ) u ON u.ts > t.week_start AND u.ts <= t.week_start + interval '1 week') SELECT week_start::DATE as week, SUM(amount) as amount FROM weekly_transactions GROUP BY 1 ORDER BY 1 `), {marker: 'circle', x: "week", y: "amount", title: d => `${d.week.toISOString().split('T')[0]}: ${d.amount}`}) ] }))38:T7d4,addTooltips(Plot.plot({ color: { legend: true }, width: 1000, style: { paddingLeft: 50, overflow: "visible" }, y: { grid: true, }, x: { type: "band", tickFormat: d => d.toLocaleString("en", {month: "narrow"}), label: null }, marks: [ Plot.barY( Object.entries(categorise(await query(` WITH monthly_transactions AS (SELECT t.month_start, u.* FROM ( SELECT * FROM generate_series( '2018-01-01'::DATE, '2022-06-01'::DATE, '1 month' ) AS month_start ) t JOIN ( SELECT "Description" AS business, "Started Date" as ts FROM ${tableName} WHERE "Type" = 'CARD_PAYMENT' ) u ON u.ts > t.month_start AND u.ts <= t.month_start + interval '1 month') SELECT month_start::DATE as month, business, COUNT(0)::INTEGER as purchases FROM monthly_transactions GROUP BY 1, 2 ORDER BY 1 `), categories)).flatMap( ([category, months]) => sumByKey( months, 'month', 'purchases', // These are needed because Javascript cant === compare Date objects. Instead, we use getTime() to get // a numeric unix timestamp which can be compared. For charting Date objects work better though, so // the last argument converts the timestamp back to a Date. x => x.getTime(), x => {return new Date(x)} ).map( ({month, purchases}) => ({month, purchases, category}))).sort( (a, b) => a.month - b.month) , { order: "sum", x: "month", y: "purchases", fill: "category", title: d => `${d.month.toISOString().split('T')[0].slice(0,-3)} ${d.category}: ${d.purchases}`}), Plot.ruleY([0]) ] })) 39:[{"id":"89918dc69b66240df7852a4f4efe0744cc8506b9352091d3a75f80740867eac93f937b8c3cc85d4de7d7b3b94aec2e8d5dce980a21b17fb64404d38b0c17837d","href":"https://static.observableusercontent.com/files/89918dc69b66240df7852a4f4efe0744cc8506b9352091d3a75f80740867eac93f937b8c3cc85d4de7d7b3b94aec2e8d5dce980a21b17fb64404d38b0c17837d","name":"observable-add-db.png","type":"image/png","lastModified":1654118450265.289,"size":381566},{"id":"64f81c403328de5cfef168ddb653b662f31815cc19997df0cb8c749b616387ea57c14b4ea902592e203c366bc23b2f622c39b1eef83a7fb9267c455d7187c761","href":"https://static.observableusercontent.com/files/64f81c403328de5cfef168ddb653b662f31815cc19997df0cb8c749b616387ea57c14b4ea902592e203c366bc23b2f622c39b1eef83a7fb9267c455d7187c761","name":"sql-creds.gif","type":"image/gif","lastModified":1654118450265.289,"size":875087}] 32:{"id":"80a7dbc573470ed1","slug":"working-with-private-splitgraph-data-in-observable","title":"Working with private Splitgraph data in Observable","thumbnail":"0a4749ce3d2ecff19ed1d2864818c0dd510029062b55860a387f9634a2fd17a7","access_level":"public","create_time":"$D2022-06-01T21:20:50.265Z","update_time":"$D2022-06-02T19:42:49.405Z","view_time":null,"first_public_time":"$D2022-06-01T21:20:57.905Z","fork":true,"stars":0,"starred":false,"owner":{"type":"team","id":"0cbaeecdc8b44038","login":"splitgraph","name":"Peter Neumark","avatar_url":"https://avatars-next.observableusercontent.com/af83882d832cc29f3e07a365f8d4ea4fd8689ffe2d49bf98a8744c1c63845615","tier":"starter_2024"},"version":450,"publish_version":450,"role":null,"authored":false,"authors":[{"type":"individual","id":"7b4451a910b36fdb","login":"splitgraph","name":"Peter Neumark","avatar_url":"https://avatars-next.observableusercontent.com/af83882d832cc29f3e07a365f8d4ea4fd8689ffe2d49bf98a8744c1c63845615","tier":null}],"members":[{"type":"individual","id":"7b4451a910b36fdb","login":"splitgraph","name":"Peter Neumark","avatar_url":"https://avatars-next.observableusercontent.com/af83882d832cc29f3e07a365f8d4ea4fd8689ffe2d49bf98a8744c1c63845615","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":2,"collection":null,"type":"notebook","body":{"title":"Working with private Splitgraph data in Observable","cells":[{"id":309,"value":"# Working with private Splitgraph data in Observable","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":313,"value":"

Make a copy of this notebook, set up the Database connection as detailed below and query your bank account statement like the conscious consumer you are! See the onboarding guide if you get stuck!

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":0,"value":"$34","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":260,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":151,"value":"viewof datasource = Inputs.radio([\"Public\", \"Private\"], {value: \"Public\", label: \"Splitgraph Repository\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":262,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":154,"value":"// The table name depends on whether we're querying the public or private repository.\ntableName = datasource === \"Public\" ? '\"neumark/revolut-sample:latest\".\"file_1\"' : '\"neumark/revolut:latest\".\"file_1\"'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":158,"value":"import {q} from \"@splitgraph/ddn\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":173,"value":"// To access the private repository, the Database Client must be initialized\ndb = datasource === \"Public\" ? md`Since we're using DDN functions, the \\`db\\` variable isn't used.` : DatabaseClient(\"Splitgraph DDN\");","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":164,"value":"query = datasource === \"Public\" ? q : (queryString) => db.query(queryString);","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":145,"value":"## Connecting to Splitgraph from Observable\nSince I wanted to plot my query results, Observable was a natural choice due to its great [Plot library](https://observablehq.com/@observablehq/plot?collection=@observablehq/plot) extended by [@mkfreeman](https://observablehq.com/@mkfreeman) to support tooltips.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":356,"value":"import {addTooltips, Plot} from \"@mkfreeman/plot-tooltip\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":381,"value":"There are two ways to query Splitgraph from Observable. You can switch between them using the [`Splitgraph Repository`](#cell-260) toggle.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":185,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":16,"value":"# Queries\n\nBank account statement imported: ✓\n\nConnected Observable notebook to my Splitgraph repository: ✓\n\nIt's time to query away!\n\n## Top 30 businesses (by total amount spent)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"topByTotalSpent = query(`SELECT \n \"Description\" AS business,\n CAST(SUM(\"Amount\")*-1 AS DOUBLE PRECISION) as total_spent\nFROM\n ${tableName}\nWHERE \"Type\" = 'CARD_PAYMENT'\nGROUP BY 1\nORDER BY 2 DESC\nLIMIT 10;`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":25,"value":"Plot.plot({\n style: { paddingLeft: 20, overflow: \"visible\" },\n width: 1200,\n height: 500,\n x: {\n domain: d3.sort(topByTotalSpent, d => d).map(d => d.business)\n },\n y: {\n grid: true\n },\n marks: [\n Plot.barY(topByTotalSpent, {y: \"total_spent\", x: \"business\"}),\n Plot.ruleY([0])\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":214,"value":"It's an interesting bar chart, but could definitely be improved.\nFirst, several businesses have Hungarian names. The _\"Állatorvos\"_ in the label of the tallest column means veterinarian.\nSecond, some of these should be categorised together.\n\n## Building expense categories\nI define each category as an array of either `string` or `RegExp` objects which will be matched with the actual business names. The `categorise()` and `getCategoryTotals()` functions are used to create the next bar chart.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":43,"value":"$36","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":49,"value":"categorise = (data, categories) => data.reduce((acc, row) => {\n const label = row.business;\n let rowCategory = 'uncategorized';\n for (let [category, patterns] of Object.entries(categories)) {\n for (let pattern of patterns) {\n if (label.match(pattern)) {\n rowCategory = category;\n break;\n }\n }\n }\n acc[rowCategory] = rowCategory in acc ? acc[rowCategory] : [];\n acc[rowCategory].push(row);\n return acc;\n }, {});","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":67,"value":"// Sum up the total amount for all rows in each category, returning an array of pairs which is easy to graph\ngetCategoryTotals = (categorizedRows) => Object.entries(categorizedRows).reduce(\n (acc, [category, rows]) => acc.concat(\n {category, total: rows.reduce(\n (acc, {total_spent}) => acc + total_spent, 0)\n }),\n [])","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":71,"value":"categorizedByTotalSpend = getCategoryTotals(categorise(await query(`\nSELECT \n \"Description\" AS business,\n -- Note: the CAST is needed so the total_spent column contains numbers not strings\n -- We multiply by -1 to make the amounts positive since spending shows up as a negative\n -- value in our account history.\n CAST(SUM(\"Amount\")*-1 AS DOUBLE PRECISION) as total_spent\nFROM\n ${tableName}\n-- Filter transactions past date when we adopted our family dog.\nWHERE \"Type\" = 'CARD_PAYMENT' AND \"Started Date\" > '2020-12-08'::DATE\nGROUP BY 1\nORDER BY 2 DESC\nLIMIT 100;`), categories))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":77,"value":"Plot.plot({\n style: { paddingLeft: 50, overflow: \"visible\" },\n width: 1000,\n x: {\n domain: d3.sort(categorizedByTotalSpend, d => -d.total).map(d => d.category)\n },\n y: {\n grid: true\n },\n marks: [\n Plot.barY(categorizedByTotalSpend, {y: \"total\", x: \"category\"}),\n Plot.ruleY([0])\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":227,"value":"The categorised chart is much more useful. Already we can tell keeping pets can indeed be a costly hobby. Also revealing is the fact that I spend more on coffee than groceries!☕\n\nThere's always stuff that doesn't exactly fit into the other categories, so having an `uncategorized` bin is hard to avoid, but we can see some of the answers to my initial questions even if this is the largest category.\n\n## Total weekly spending\nThe following chart shows that weekly spending does vary quite a bit (though the publicly available sample table distorts considerably).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":281,"value":"$37","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":377,"value":"While being able to chart weekly spending is nice, it would be easier to correlate changes in spending habits with real-world events (COVID-19 outbreak, adopting a dog, etc) if the weekly spending was broken down by category.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":290,"value":"## Weekly spending by category","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":316,"value":"sumByKey = (arr, keyField='week', valueField='amount', keyEncode=(x)=>x, keyDecode=(x)=>x) => [...(arr.reduce((acc, row) => {\n const key = keyEncode(row[keyField]);\n acc.set(key, (acc.get(key) ?? 0) + row[valueField]);\n return acc;\n}, new Map()).entries())].map(([key, value]) => ({[keyField]:keyDecode(key), [valueField]:value}))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":294,"value":"weeklyCategorySpend = Object.entries(categorise(await query(`\nWITH weekly_transactions AS (SELECT\n t.week_start,\n u.*\nFROM (\n SELECT * FROM generate_series(\n '2018-01-01'::DATE,\n '2022-06-01'::DATE,\n '1 week'\n ) AS week_start\n) t\nJOIN (\n SELECT\n \"Description\" AS business,\n \"Started Date\" as ts,\n -- Note: the CAST is needed so the total_spent column contains numbers not strings\n -- Multiply by -1 to make the amounts positive since spending shows up as a negative\n -- value in the account statement.\n CAST(\"Amount\" * -1 AS DOUBLE PRECISION) as amount\n FROM\n ${tableName}\n WHERE\n \"Type\" = 'CARD_PAYMENT'\n) u ON\n u.ts > t.week_start AND\n u.ts <= t.week_start + interval '1 week')\nSELECT\n week_start::DATE as week,\n business,\n SUM(amount) as amount\nFROM weekly_transactions\nGROUP BY 1, 2\nORDER BY 1\n`), categories)).flatMap(\n ([category, weeks]) => sumByKey(weeks).map(\n ({week, amount}) => ({week, amount, category}))).sort(\n (a, b) => a.week - b.week)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":337,"value":"addTooltips(Plot.plot({\n color: {\n legend: true\n },\n width: 1000, \n style: { paddingLeft: 50, overflow: \"visible\" },\n y: {\n grid: true,\n },\n x: { \n type: \"band\",\n tickFormat: d => d.toLocaleString(\"en\", {month: \"narrow\"}),\n label: null\n },\n marks: [\n \n Plot.barY(weeklyCategorySpend, {\n order: \"sum\",\n x: \"week\",\n y: \"amount\", \n fill: \"category\", \n title: d => `${d.week.toISOString().split('T')[0]} - ${d.category}: ${d.amount}`}),\n Plot.ruleY([0]) \n ]\n}))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":383,"value":"## Monthly purchase count by category\n\nTo understand where our money is going, the previous chart plotting total amounts by category is best. How often a transaction of a particular category is made can be revealing of habits - regardless of the amount spent. Grouping purchase counts by week was too granular. It made more sense to group by month instead.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":385,"value":"$38","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":449,"value":"# Conclusion\n\nObservable notebooks are a great way to analyse, plot and share our understanding of data. Splitgraph provides a convenient way to [import CSV](https://splitgraph.com/upload) files, and to query them both on the Splitgraph website and from Observable notebooks. Public and private data can be queried with equal ease. While the former does not require the creation of a database connection object, the later does, limiting its use to private notebooks. For private data, private notebooks make sense, so this is a reasonable constraint.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"}],"files":"$W39","stdlib":"1","titling":"auto"}} 33:"$32:body"