I love the You Need A Budget (YNAB) application for taking control of my budget. I’ve been a proud & vocal fan for almost a decade! So much so; that I convinced my wife to start using it as well. However, there was one snag! Her bank, Bancolombia, was not supported by YNAB’s built-in integrations. This is problematic because she would have to manually input all her transactions. But as you may have already guessed from the title of this article, I was able to set up auto-syncing between YNAB and Bancolombia using some free tools and an open mind.
Disclaimer — the solution I provide here is not “perfect”. Changes in the Bancolombia email format could cause this to not work. I will do my best efforts to keep this article updated with any patches or fixes.
Quick Overview
Without getting into heavy nitty gritty details. I think it is helpful to understand at a high level what this solution entails so you know generally. Below is a rough illustration:
Now that you know roughly what this is about… let’s get started!
The Step-by-Step Guide
This guide assumes you already have a YNAB account. If you don’t have one yet, you can get one here. Moreover, make sure that your YNAB budget is using COP as the currency!
Step 1 — Set up Bancolombia to send emails to your Gmail account and label them.
Make sure that your email notifications are turned on in your Bancolombia account and that they are being sent to your Gmail account. You’ll want to set up a filter so that you automatically label incoming emails. This label will come in handy in the next few steps!
Set up a filter to auto-label transaction emails incoming from alertasynotificaciones@notificacionesbancolombia.com.
Transaction notification emails coming in from Bancolombia should now be labeled automatically with your label of choice.
Step 2 — Get the access token and required information for your YNAB account.
In order to create transactions in your YNAB account, we need to get some key pieces of information. Namely, we’ll want:
- Access token; so we have the permission to add transactions to your YNAB account.
- Budget ID; so we know which budget to put the transactions on
- Account ID; so we know which account in the budget you want to transact in.
Let’s first get the Account ID and Budget ID. Click into one of your accounts within your budget of choice in the YNAB web interface. The URL that appears contains the needed values:
https://app.youneedabudget.com/BUDGET ID/accounts/ACCOUNT_ID
Copy and save these for later! We will need them in the following step.
Next, let’s get an access token. Go to the “Account Settings” page and then to the “Developer Settings” page. Under the “Personal Access Tokens” section, click “New Token”, enter your password and click “Generate” to get an access token. Copy and save the account token. Do not share this with anyone!
You should now have the Access token, Budget ID, and Account ID for your YNAB account.
Step 3 — Create a free account with Zapier
Zapier is a pretty great tool if you want to integrate multiple systems… like what we are about to do with Gmail and a bit of code that I’ll provide.
If you don’t already have a Zapier account, you can make a free one here.
Step 4 — Setup your Zap
Okay, the following step is a bit mechanical, but we’ll get through this!
- Log in to your Zapier account
- Click on “+ Create Zap”
- Set the first trigger to be from “Gmail” and the event to be “New Email”.
- Choose your account and sync it with Zapier
- In the “Set up Trigger” step set the Label/Mailbox to the label, you are automatically putting on Bancolombia emails (refer to Step 1).
- In the “Test Trigger” step, you can load a previous email or just skip this step by clicking “Continue”
- Click the “+” button to add an Action. Select “Code by Zapier”
- In the “Choose app & event” step select “Run Javascript” as the Event.
- In the following “Set up action” step, set the Input Data to have a “snippet” with the value “Raw Snippet”. See the image below:
10. In the “Code” section, copy and paste the following snippet into the field. This code a high-level extracts the transaction details and sends them to YNAB to create a transaction.
// -------------------------------------------------------
// Bancolombia Email -> YNAB Transaction (v1 / 2022-28-10)
// -------------------------------------------------------
// By: Edwin Mak
//
// --- Introduction ---
// This script is intended to be used within
// a Zapier workflow. It will take the text
// from a Bancolombia trasanction email and
// output the payee, total amount and date
// which can be used to create a new YNAB
// transaction.
//
// --- Instructions ---
// Set the following variables from YNAB:
const budgetID = 'REPLACE ME WITH BUDGET ID'
const accessToken = 'REPLACE ME WITH ACCESS TOKEN'
const accountID = 'REPLACE ME WITH ACCOUNT ID'// -------------------------------------------------------
// --- Function Definitions Start Here -------------------
// -------------------------------------------------------/**
* Outputs the payee and total amount of the transaction
* using the snippet text from a Bancolombia transaction email.
* param {string} snippet - The snippet text from the email.
* returns {object} - The payee, date, and total amount
*/
function extractPayeeAndTotal(text) {
/**
* Extract only the important part of the text
*/
const extractedText = text.match(/le informa.*\./i)[0];/**
* Determine first if the transaction is an inflow or outflow by
* matching against variant formats of the snippet
*/
if ((/compra por/i).test(extractedText)) {
let extractionResults = extractedText.match(/\$(\S*)\s(?:en|por)\s(\D*)/);
let dateExtractionResults = extractedText.match(/(\d*\/\d*\/\d*)/)[0].split(/\//);
return {
type: 'outflow',
payee: extractionResults[2].trim(),
date: dateExtractionResults[2] + '-' + dateExtractionResults[1] + '-' + dateExtractionResults[0],
total: "-" + extractionResults[1].replace(/\D/, '').replace(/(,|\.)/, '')
}
} else if((/\spago\s\por\s/i).test(extractedText)) {
let extractionResults = extractedText.match(/\spago\s\por\s\$(\S*)\sa\s(.*)\sdesde/i);
let dateExtractionResults = extractedText.match(/(\d*\/\d*\/\d*)/)[0].split(/\//);
return {
type: 'outflow',
payee: extractionResults[2].trim(),
total: "-" + extractionResults[1].replace(/\D/, '').replace(/(,|\.)/, ''),
date: dateExtractionResults[2] + '-' + dateExtractionResults[1] + '-' + dateExtractionResults[0]
}
} else {
let extractionResults = extractedText.match(/de\spago\sde\s(.*)\spor\s(.*)\sen/);
let dateExtractionResults = extractedText.match(/(\d*\/\d*\/\d*)/)[0].split(/\//);
return {
type: 'inflow',
payee: extractionResults[1].trim(),
date: dateExtractionResults[2] + '-' + dateExtractionResults[1] + '-' + dateExtractionResults[0],
total: extractionResults[2].replace(/\D/, '').replace(/(,|\.)/, '')
}
}
}/**
* Creates a new transaction in YNAB
* param {object} transactionData - The payee, date, and total amount
* param {string} budgetID - The ID of the budget to create the transaction in.
* param {string} accessToken - The access token to use to authenticate with YNAB.
* param {string} accountID - The ID of the account to create the transaction in.
* returns {string} - response from YNAB
*/
async function createYNABtransaction(transactionData, budgetID, accessToken, accountID) {
const res = await fetch(`https://api.youneedabudget.com/v1/budgets/${budgetID}/transactions`, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'Authorization': `Bearer ${accessToken}`
},
body: JSON.stringify({
"transaction": {
"account_id": accountID,
"date": transactionData['date'],
"amount": transactionData['total'] + "0",
"payee_name": transactionData['payee'],
"cleared": "cleared",
"approved": true,
},
})
})const text = await res.text();return text;
}// -------------------------------------------------------
// --- Function Definitions End Here ---------------------
// -------------------------------------------------------// -------------------------------------------------------
// --- Main Script Starts Here ---------------------------
// -------------------------------------------------------// Extract data from the email
const transactionData = extractPayeeAndTotal(inputData.snippet);
// Use that data to create a new transaction in YNAB
const response = await createYNABtransaction(transactionData, budgetID, accessToken, accountID);// Output results to Zapier so it can be viewed
output = [{
snippet: inputData.snippet,
transactionData: transactionData,
results: response
}]// -------------------------------------------------------
// --- Main Script Ends Here -----------------------------
// -------------------------------------------------------
11. In the code, insert the YNAB values that you obtained in the previous step near the top where it says “REPLACE ME….”:
// --- Instructions ---
// Set the following variables from YNAB:
const budgetID = 'REPLACE ME WITH BUDGET ID'
const accessToken = 'REPLACE ME WITH ACCESS TOKEN'
const accountID = 'REPLACE ME WITH ACCOUNT ID'12. In the “Test Action” step, you can either test this code for a previous email or just click “Publish Zap”
Step 5 — Test it out!
Now when you make transactions with your Bancolombia account, you should receive an email alert that Zapier then uses to extract transaction data and send it over to YNAB. The next time you access YNAB, you should see the transaction added with the payee, date, and amount!
Conclusion
You should now not have manually inserted Bancolombia transaction details into your budget anymore! I imagine this guide was a bit laborious to follow but in the long run, this beats having to manually punch in numbers to your budget!
Let me know if this helped you out or if you have any questions. And if you are feeling generous you can buy me a coffee here.
