![]() ![]() We’re using lower case for column names with underscores _ instead of spaces. In our table, they’ll be called created_at ( date is a reserved word in SQL and it’s better not to use it as a column name), payee, account_number, etc. Let’s examine what columns will we need by looking at the first row in the CSV file:ĭate, Payee, Account number, etc. Now we need a table to host the transactions from the CSV file, let’s call it n26_transactions (if you use Chase bank it’ll be chase_transactions). Let’s call our database personal_finance in case you want to add more data in the future (other bank accounts, investments, etc). To create a new database you’ll need to run a query from the template ( template1 in case of PostgreSQL) database: CREATE DATABASE personal_finance I’ll create a simple database via the client’s interface:Īlternative approach is to use SQL queries for database or table creation as well. I’ll use a PostgreSQL database with a free client called Postico. In the next couple of steps we’ll replicate this table in the actual SQL database and copy the data. Let’s inspect the CSV file with transactions, it should look something like this:īasically, CSV files are tables. In case you have a problem with anything just leave a comment below or ping me on Twitter. Here’s a link for the N26 report file that will be used in this blog post (the data is made up, of course, but the format is real).įeel free to download the transactions from you bank and follow along with them! ![]() Let’s download the CSV file with our bank transactions. Let’s get started! Importing the data Step 0: Inspecting the CSV report Looking at our goals I’d imagine the final result like this: Such visualization helps a lot to anticipate intermediate steps and bottlenecks. To kick it off I usually try to visualize the result set that our final query will return. To answer these questions we’ll have to write one (potentially massive) SQL query. Here I want to know which companies charge me regularly, for how long and how much money have I already paid. In my experience, any analysis (or anything in general) has much higher chances of success if it has a goal. It’s not far from the reality of data analysts, marketers and product managers mining their data. What we are about to do is a typical data research or an Ad Hoc analysis. Let’s download it and improvise a simple subscription detection mechanism based solely on SQL. Good news are that N26, as any other bank, allows to download all transactions as a CSV file. I’ve no idea how N26 detects subscriptions and why it’s broken for me. Drum roll… it was empty! I definitely pay for some subscription services (Spotify, for example ) but none of them showed up in the report. I went to my N26 bank’s Subscriptions report hoping to find a nice list of recurring transactions there. It’s 2020 and I thought that bank apps are already smart enough to detect subscriptions. This story had a happy ending but it made me wonder how many such recurring transactions I haven’t noticed. You know what was next: I wrote to customer support, asked for a refund and after some time got my 200$ back. It was from a service I thought I’ve canceled. Recently I spotted a strange 200$ transaction in my bank statement. BONUS: Transactions with dynamic payee name.Step 5: Calculating transactions frequency.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |