by zacce
Quicken user for 20+ yrs. My 2016 Quicken expired and I decided to create a spreadsheet to replace Quicken. I replicated everything I need to manage my investments/spending/income.
Here’s the bare bone version:
docs.google.com/spreadsheets/d/1rt14NzYB3OcZ2jLqnJAp3YkhV7R25ipjjkQiyVVmBfs/copy (It will ask you to make a Google Sheet copy.)
This basic version has 5 tabs:
- NET WORTH (or account balances)
months in rows, accounts in columns - INCOME EXPENSE
months in rows, categories in columns - PORTFOLIO (for # of shares, prices)
months in rows, securities in columns - Data1 (for bank/CC/loan transactions)
columns: account, date, payee, category, amount - Data2 (for investment transactions)
columns: account, date, type, symbol, price, shares, $ amount)
How it works:
Enter bank/CC/loan transaction data into “Data1” and investment transactions into “Data2”. (This copy has a fictional example for demonstration. See #16 below to semi-automate data entry.)
Then the Google sheet auto-updates the 1st 3 report tabs through
- Pivot Table calculating how much you spent on each category, each month
- Pivot Table calculates how many shares added/subtracted for each security, each month
- =googlefinance() pulls historical end of month security prices. Multiplied by # of shares to calculate the value for each security, each month
- =sumifs(amount, account, date) calculates end of month balances for every account
Additional consideration and functions/features you may add to this basic version (My full version is a lot more complicated than the above bare bone. I removed the complexity on purpose so that ppl can understand how the core function works and modify to suit their needs. Nevertheless, I’m willing to help how to add the functions below.):
- “Dashboard” tab to display the current account balances, line/bar/pie charts for portfolio value/spending/asset allocation (similar to Quicken homepage)
- Double clicking a cell in Pivot Chart creates a new tab displaying only the corresponding transactions
- “Running balance” for account reconciliation
- Use “filter” in pivot table to exclude unwanted categories such as “Transfer” in spending report
- Accrual-basis accounting (vs cash-basis). For example, tax refund received on 4/15/2019 should be recognized as for year 2018 (not 2019).
- Expensing over time (vs lumpsum expense)
- Split transaction (eg. mortgage pmt = interest expense + principal pmt)
- Recognize gross income (vs net income)
- Dividend/capital gains income
- Cost-basis, unrealized capital gains, dollar-weighted return
- Asset allocation (eg. Stock vs Bond %)
- Data validation (selecting field from a list)
- =importrange() (useful if your data becomes too large)
- =iferror() (to hide #N/A results)
- Excel doesn’t support =googlefinance() to pull historical prices. As of now, Excel can only pull current information. Consider using the last recorded price or a 3rd party add-in, if you want to use Excel.
- Download transaction CSV files from websites and copy/paste data (vs hand entry). If you are willing to share passwords, consider mint, tillerhq to import data.
- Learn how to convert QIF to CSV, if you are migrating from Quicken
- Sort transactions in descending vs ascending date order
- Conditional formatting based on account name in Data tabs
Let me know if you want to know more about these with examples.