search

dckc / finquick

family finance tools of a closet librarian
https://www.madmode.com/search/label/finances/
MIT License
12 stars 4 forks source link

SheetSync integration: Plaid -> Google Sheets #45

Closed dckc closed 1 year ago

dckc commented 1 year ago

Lunchmoney does much of what I wanted to do by way of a web interface to my accounts via Plaid, but lack of thorough double-entry accounting means I'm still using GnuCash. Plus, I use Google Sheets for all sorts of ad-hoc stuff. What about going straight from Plaid to Google Sheets? Is that a thing? Indeed it is!

I'm pretty happy with the results of the two week free trial. Some of my goals were:

Accounts with codes

Setting up a few accounts worked as expected. I added a column for account code for syncing with my GnuCash Chart of Accounts.

The Balances sheet works nicely.

Categories with codes

Exporting my GnuCash income / expense accounts for use as Categories was straightforward:

Sqlite recursive queries are fun!

Shout-out to DBeaver, my favorite SQL IDE these days.

TODO:

Transactions: Fill in Venmo details from email

My bank reports Venmo transactions as just "Venmo". Finding the payee and memo from email is tedious, so I made a Custom Menu with some Custom Functions using Google Apps Script.

Screenshot at 2023-02-25 22-21-34

Handy refs:

Transactions: Fill in Amazon details from reports

Amazon provides CSV exports of Orders and Items. I import those into nearby sheets and then when I have a transaction selected, I use Tx Lookup to add the items to the transaction memo, which makes it easier to categorize. Tx Lookup will look up Amazon or Venmo depending on the Description.

Screenshot at 2023-02-25 22-08-02

TODO:

Transactions: Push GnuCash Ids to Sheetsync

This adds a yarn push-txids CLI tool to annotate Sheetsync transactions with GnuCash ids:

/**
 * For each row that does not yet have a tx_guid,
 * look it up in split_detail by date, amount, and account code.
 * Update tx_guid and online_id in Syncsheets.
 */
export const pushTxIds = async (sc, gc, { offset, limit = 3000 } = {}) => {

split_detail is a join of the GnuCash transactions, splits, and accounts tables.

Transactions: Pull Categories from Sheetsync to GnuCash

Then I use yarn pull-categories:

/**
 * For uncategorized GnuCash transactions (account code 9001)
 * apply category from Sheetsync where available.
 */
const pullCategories = async (sc, gc, { offset = 0, limit = 3000 } = {}) => {
dckc commented 1 year ago

tab dump:

Sheetsync • Automatically sync your finances with your spreadsheet https://getsheetsync.com/

Sheetsync - Google Workspace Marketplace https://workspace.google.com/marketplace/app/sheetsync/198068442022

Class Utilities  |  Apps Script  |  Google Developers https://developers.google.com/apps-script/reference/utilities/utilities?hl=en#parseDate(String,String,String)

clasp - The Apps Script CLI https://codelabs.developers.google.com/codelabs/clasp/#3

Custom Functions in Google Sheets  |  Apps Script  |  Google Developers https://developers.google.com/apps-script/guides/sheets/functions

Custom Menus in Google Workspace  |  Apps Script  |  Google Developers https://developers.google.com/apps-script/guides/menus

How To Download Your Amazon Order History Report In 3 Steps https://www.tillerhq.com/how-to-download-your-amazon-order-history-report/

dckc commented 1 year ago

Lunchmoney does much of what I wanted to do by way of a web interface to my accounts via Plaid, but lack of thorough double-entry accounting means I'm still using GnuCash. Plus, I use Google Sheets for all sorts of ad-hoc stuff. What about going straight from Plaid to Google Sheets? Is that a thing? Indeed it is!

I'm pretty happy with the results of the two week free trial. Some of my goals were:

Accounts with codes

Setting up a few accounts worked as expected. I added a column for account code for syncing with my GnuCash Chart of Accounts.

The Balances sheet works nicely.

Categories with codes

Exporting my GnuCash income / expense accounts for use as Categories was straightforward:

Sqlite recursive queries are fun!

Shout-out to DBeaver, my favorite SQL IDE these days.

TODO:

Transactions: Fill in Venmo details from email

My bank reports Venmo transactions as just "Venmo". Finding the payee and memo from email is tedious, so I made a Custom Menu with some Custom Functions using Google Apps Script.

Screenshot at 2023-02-25 22-21-34

Handy refs:

Transactions: Fill in Amazon details from reports

Amazon provides CSV exports of Orders and Items. I import those into nearby sheets and then when I have a transaction selected, I use Tx Lookup to add the items to the transaction memo, which makes it easier to categorize. Tx Lookup will look up Amazon or Venmo depending on the Description.

Screenshot at 2023-02-25 22-08-02

TODO:

Transactions: Push GnuCash Ids to Sheetsync

This adds a yarn push-txids CLI tool to annotate Sheetsync transactions with GnuCash ids:

/**
 * For each row that does not yet have a tx_guid,
 * look it up in split_detail by date, amount, and account code.
 * Update tx_guid and online_id in Syncsheets.
 */
export const pushTxIds = async (sc, gc, { offset, limit = 3000 } = {}) => {

split_detail is a join of the GnuCash transactions, splits, and accounts tables.

Transactions: Pull Categories from Sheetsync to GnuCash

Then I use yarn pull-categories:

/**
 * For uncategorized GnuCash transactions (account code 9001)
 * apply category from Sheetsync where available.
 */
const pullCategories = async (sc, gc, { offset = 0, limit = 3000 } = {}) => {
dckc commented 1 year ago

p.s. idea: