YNAB & Google Sheets

YNAB is a budgeting tool that essentially digitizes the envelope budgeting system. It can automatically import transactions and generate charts to visualize spending patterns and net worth growth. By design, YNAB is for budgeting - it isn’t meant for tracking or planning investments. You can add accounts that track the balance of your investments or properties, but they won’t contribute to the budget and they can only be updated manually.

Google Sheets has a GOOGFINANCE function that can pull recent quotes for stocks, funds, and cryptos. We’ve aggregated our investments from our retirement, brokerage, and crypto accounts into a single spreadsheet. We also track goals and plans in another sheet that summarizes our entire financial picture.

As the number of accounts grew, it took longer and longer to sync the two apps at the end of each month. With a little bit of JavaScript, I automated the synchronization process so now YNAB and Google Sheets update each other multiple times an hour instead of me updating them once a month.

Solution

YNAB and Google Sheets both offer free, well documented API’s (YNAB’s api is included with a subscription to the app). The main idea is simple: get data from one API, transform it, and send it to the other API.

Some things to consider before jumping into implementation:

YNAB has a rate limit of 200 request per hour per token
Google Sheets has a rate limit of 60 requests per minute
Transactions from YNAB aren’t frequent - usually are imported once a day
Transactions are typically imported 2-3 days after occurring, but can be manually input / edited any time
Stock prices vary throughout the day, mutual funds are priced at the end of the day
Stocks don’t change outside of trading hours - specifically, Google Sheets only pulls quotes during trading hours. So no pre-market, after market, or dark pool updates
I am not constantly checking YNAB and Google Sheets for updates

The main takeaways are updates from YNAB to Google Sheets are infrequent, updates from Google Sheets to YNAB are frequent during trading hours, and we can make several updates per hour while staying within YNAB’s rate limit, but I’m not concerned about instant updates.

I decided to use Node.js to write two separate scripts that run on cron jobs during hours that I’m usually awake. One script is responsible for generating a summary of our net worth in Google Sheets and the other script is responsible for updating our investment accounts in YNAB with the latest info from Google Sheets.

Summary Script

This script gets the balance of each account from YNAB and then groups the balances by account type (checking, savings, retirement, crypto, investment, etc). Then it writes the summary to a specific section in my spreadsheet. I highlighted and marked the range as protected so that only the script would edit those cells. It also writes a timestamp of the last run.

Since the Investments Script will update YNAB with the latest investment balances, this script doesn’t need to pull investment data from Google Sheets before making the summary.

Monthly Snapshots

I keep a history of these summaries on a monthly basis. At the end of each month I copy the latest update to a separate section of the spreadsheet. What happens if I forget to make the copy on the last day of the month? And what about transactions that haven’t been imported yet? To handle both of these cases the script will calculate last month’s summary and write it next to the current summary in Google Sheets during the first week of the month.

# Calculating last month’s balances

For each account
	Last_months_balance  = current balance - 𝚺 transactions since 1st of the month

Investments Script

This script pulls investment data from Google Sheets and groups the values by account. Then it pulls the current account balance stored in YNAB and calculates the adjustment. Last, it updates each of those transactions in YNAB.

To enable monthly tracking, the bot creates a transaction for each account for each month. The amount of the transaction is the change between last month’s balance and the current balance. Alternatively, the script could create a transaction for each day, but I didn’t find the day to day history useful and it cluttered the account in YNAB.

# Calculating transaction amount

For each investment account:
	Delta = current balance - transaction balance
	Updated transaction amt = current transaction amt + delta

When a new month rolls around, the script will create a new transaction. If one hasn’t been created yet, it will do so.

Implementation

YNAB Set Up

The docs will guide you through getting access to the api and how to call it with your favorite language. They have libraries for JS and Ruby, but the requests are simple enough to send with your favorite http client. Refer to these docs for endpoint descriptions.

I found it helpful to get familiar with the API by using Postman.

Google Sheets Set Up

Google Sheets has extensive docs for working with their API in multiple languages. I chose to write my scripts with Node.js and used the googleapis library. I also opted to authorize my script by creating a service account authorized to edit our spreadsheet.

Writing the Scripts

Once you have access to both API’s and are familiar with how to use them, the rest is trivial.

// Writing to Google Sheets

const writeToGoogleSheets = async (balances, cells) => {
  return sheets.spreadsheets.values.update({
    auth: googleAuth, // auth of service account
    spreadsheetId: '1*****', // the id of the spreadsheet. You can get this from the url /spreadsheets/d/<spreadsheetId>
    range: cells, // i.e. ‘Sheet 1’!A1:A10
    valueInputOption: 'USER_ENTERED',
    requestBody: {
      values: balances // an array of account balances
    }
  })
}

// Reading from Google Sheets

const getDataFromGoogleSheets = async () => {
  const resp = await sheets.spreadsheets.values.get({
    auth: googleAuth, // auth of service account
    spreadsheetId: '1*****', // the id of the spreadsheet
    range: range', // i.e ‘Sheet 1’!A1:A10
  })

  return resp.data.values.filter(row => row.length)
}

// Get Budget and Account Balances from YNAB

const getBudgetWithAccounts = async () => {
  const { data } = await axios.get('/budgets?include_accounts=true', options)
  const { budgets } = data.data

  return budgets.find(b => b.name === budgetName)  
}

Deploying

The scripts are stateless so they can run them from any machine at any time (i.e. Raspberry Pi, web server, AWS Lambda).

Solution#

Summary Script#

Monthly Snapshots#

Investments Script#

Implementation#

YNAB Set Up#

Google Sheets Set Up#

Writing the Scripts#

Deploying#