Record keeping with Excel

[jcheyman]

I'm new to investing and I've been reading here and there about the importance of tracking your wealth from year to year so you can see if you're hitting your goals and where you need to make readjustments etc. I would like to start an Excel file to do just that, but it's a little overwhelming trying to figure out where to start. Does anyone have any templates or basic ideas for the types of information one should keep track of and how to go about doing it?

Thanks,
JH

[cheese_breath]

I have set of Excel spreadsheets I use to manage my expenses and track my investments. It's probably more than you are looking for, but it may give you some ideas for constructing your own spreadsheets. I'd be happy to send you some conceptual models with fictitious numbers if you will PM me with your Email address.

[dumbbunny]

https://www.bogleheads.org/wiki/Household_budget

[Doc]

Does anyone have any templates or basic ideas for the types of information one should keep track of and how to go about doing it?

Bite the bullet and buy Quicken or some equivalent every three years. The extra cost is far less than the cost of the time you will use up messing with Excel.

[CantPassAgain]

Does anyone have any templates or basic ideas for the types of information one should keep track of and how to go about doing it?

Bite the bullet and buy Quicken or some equivalent every three years. The extra cost is far less than the cost of the time you will use up messing with Excel.

Your "cost of the time used up messing with Excel" is my "learning a valuable skill"

Seriously, knowing your way around Excel is a great tool to have in anyone's toolkit. It's one skill that you can apply in just about any job, IMO.

[CABob]

Does anyone have any templates or basic ideas for the types of information one should keep track of and how to go about doing it?

Bite the bullet and buy Quicken or some equivalent every three years. The extra cost is far less than the cost of the time you will use up messing with Excel.

Your "cost of the time used up messing with Excel" is my "learning a valuable skill"

Seriously, knowing your way around Excel is a great tool to have in anyone's toolkit. It's one skill that you can apply in just about any job, IMO.

Well, I'll take a middle of the road approach and suggest that you initially use Quicken or a similar program and then at some future point (couple of years?) you may have a better idea of what you want to track and develop Excel programs to do that.

[cricket49]

Does anyone have any templates or basic ideas for the types of information one should keep track of and how to go about doing it?

Bite the bullet and buy Quicken or some equivalent every three years. The extra cost is far less than the cost of the time you will use up messing with Excel.

+1

I installed it over the weekend. It will download all your data from your brokerage accounts and banks. If you are starting out your history should download without many errors that need to be corrected. Overall, it is an excellent tool to view your finances and spending all in one place. Also to calculate asset allocations and net worth.

[radix07]

If you want to get real nerdy... I use mint.com to track all of my accounts. Then I have a Python script that uses this https://github.com/mrooney/mintapi to pull all of my balances from mint.com and into my own spreadsheets in Google Docs. Then I have a quick script in google docs that adds another entry into my networth tracker when I want to add a sample...

I got sick of manually updating everything and didn't like the way mint.com was tracking things. Now it take a button press to bring everything up to date in my spreadsheets.

Wouldn't recommend this to most people.

[midareff]

Your "cost of the time used up messing with Excel" is my "learning a valuable skill"

Seriously, knowing your way around Excel is a great tool to have in anyone's toolkit. It's one skill that you can apply in just about any job, IMO.

+1 I keep detailed records in excel and can identify every lot bought or sold in the last 18 years for whatever that's worth. I have excel set to import certain data sets from Morningstar (M*) including daily price, various previous 12 month data and other things such as SEC, duration of bond funds and so forth. I use a page for each holding and let excel bring the current # shares from the appropriate work sheet which has all buys, sells, distributions, etc.

Between all the data M* can track, which you can import in seconds, you will probably find your needs for information change over time. Excel is easy to work with, makes an excellent acquired skill and can be customized as you go along to give you what you want as you decide what is and what isn't important to you. It's also easy to tie workbooks together so income numbers can be picked up by a budget book, etc. Start anywhere and as your needs evolve have excel evolve with you.

[midareff]

If you want to get real nerdy... I use mint.com to track all of my accounts. Then I have a Python script that uses this https://github.com/mrooney/mintapi to pull all of my balances from mint.com and into my own spreadsheets in Google Docs. Then I have a quick script in google docs that adds another entry into my networth tracker when I want to add a sample...

I got sick of manually updating everything and didn't like the way mint.com was tracking things. Now it take a button press to bring everything up to date in my spreadsheets.

Wouldn't recommend this to most people.

BTW, M* exports excel sheets containing anything you tell the portfolio tracker to display for you. When you open excel it will update against that fresh data if you have your excel so programmed. Open portfolio tracker, export, open excel, done!.

[radix07]

BTW, M* exports excel sheets containing anything you tell the portfolio tracker to display for you. When you open excel it will update against that fresh data if you have your excel so programmed. Open portfolio tracker, export, open excel, done!.

Will have to look at that to track my investments a little closer. But my main concern is my overall balances and net worth. I pretty much just use a 3-fund portfolio which I track with a simple ticker import supported with the GOOGLEFINANCE function.

But there are plenty of ways to skin this cat, whatever works best for you is the most important thing.

[jebmke]

Will have to look at that to track my investments a little closer. But my main concern is my overall balances and net worth. I pretty much just use a 3-fund portfolio which I track with a simple ticker import supported with the GOOGLEFINANCE function.

You should also keep track (either externally or within your institution's system) of unrealized gains and losses so you can harvest losses in taxable accounts.

[radix07]

You should also keep track (either externally or within your institution's system) of unrealized gains and losses so you can harvest losses in taxable accounts.

I agree with you on that, haven't found a straightforward way to pull balances and transactions directly from vanguard with my methods. So that part of things still requires some manual investigation. Although I haven't run into a situation where I could harvest any losses yet, as I have just recently started investing in taxable accounts the last couple years...

[Bill M]

Excel is a very powerful tool. Start by creating a workbook with investment account values; but you can do so much more. Add summary pages (aka net worth) with the quarterly (or monthly) figures, and add a graph of them over time. For taxable accounts, also record purchase dates and basis values; you know potential capital gains or tax losses for harvesting. As a reminder to download all the paperless statements of the accounts, add a "todo" page (formulas of the form =Date(year(today()),month(today()), 15) for something on the 15th of the month). Once you have the "todo" page, add all the other household paperless statements, like credit cards (Excel can act as a reminder to pay them all on time), and utility bills. When you download a utility bill, add the current usage figures to a "Utilities" worksheet, and add a graph of usage over time. When you download a checking account statement, also get the detail transactions from the bank; great for tracking total spending over the years. For annual expenses, like insurance premiums, real estate taxes, termite inspections, etc, an entry in the "todo" worksheet means they won't be forgotten. Projecting the future also -- how much will I be pulling from my taxable investments in 2035? Not that the number is any good, but thinking about it is.

[bengal22]

I do a simple balance sheet that I update monthly. I split it out by taxable, non-taxable, investment sum, house, total, liabilities, net worth. I do some formulas of investments to track performance on investment accounts(non-cash) to compare with benchmarks - Vanguard Target 2015,2020, Vanguard s&p 500, Madsingers' coffeehouse and 3 fund portfolio. I do not use graphs because I had enough of graphs when I worked.


Jan Feb etc
Bank A
Cd
Mutual Fund A
Taxable total

Mutual Fund A
Mutual Fund b
Etc
Non-Taxable total

Investment total

House Value
Asset Total

Mortgage
Credit Card(pay off monthly)
Liability Total

Net Worth

[jebmke]

I am planning to port my Quicken taxable account detail to Excel this year. The only thing I have used it for lately is maintaining cost basis info for my taxable accounts. My goal has been to simplify things to the point where Quicken is overkill. The only things I need to keep track of now is Equity vs. Fixed for re-balancing and unrealized gains and losses in taxable for TLH opportunities. Nothing else is actionable for me.

For expense tracking, annual expense (non-capital) and capital (e.g. heat pump replacement, new deck) is the most granular I need. I enter these two numbers monthly from the bank statement.

[sharpjm]

I work with 2 files, although they could be consolidated. The first is a pretty large file, it tracks the balance history of my retirement accounts and calculates the annualized return of my accounts since inception. Then it extrapolates to retirement, assuming continued contributions and rate of return, so I can see the potential balance at any future date. It is a big file so I keep it separate. I don't update it often.

The second file is smaller. It tracks assets, debts, and net worth. I manually enter values for assets and debts each month and plot the history so I can see how much net worth changes each month. I have a separate tab for keeping track of taxable account cost basis. I also keep close track of asset allocation in this file as well.

IMO, using excel for closely tracking budget, meaning each and every individual expense, is overkill. Mint has a good layout for budget and does the work for you.

[pshonore]

While on this subject, whats the best way of pulling current prices for stocks, mfs, etfs, etc into a spreadsheet? The Microsoft "method" hasn't worked in months. I'm currently using the PSQ function in Excel but that only pulls last price and doesn't always work.

[midareff]

While on this subject, whats the best way of pulling current prices for stocks, mfs, etfs, etc into a spreadsheet? The Microsoft "method" hasn't worked in months. I'm currently using the PSQ function in Excel but that only pulls last price and doesn't always work.

I get pricing and other data from an excel export from M*. My excel workbook imports that data when opened and does all the calculations I want from there. Just be careful to enable editing when you import and save so the numbers can be pulled. I pull tons of data from share prices to prior 12m performance, 12 M dividends, duration, SEC, YTD and more.

[MN-Investor]

Unless you're a spreadsheet maven, it's best to start simple, focusing on the primary purpose of the spreadsheet you're creating. Personally, I would suggest starting with a spreadsheet like bengal22 suggested. List your accounts down the left side. List the taxable accounts, subtotal them, list the tax-deferred accounts, subtotal them, then have a grand total. Each column would be a month end, or however often you want to track assets.

By the way, I only track the value of my portfolio. I don't include my home, etc. Our only debts are monthly charge cards, so I omit those too. But include in your spreadsheet the items that are important for *you* to track.

For the longest time, the above spreadsheet was all I updated. It was easy since it just listed Scottrade Brokerage, Wife 401(k), etc. But more recently we have been concerned about our asset allocation. I made a second spreadsheet which I only occasionally update. It lists all the accounts down the left side and starts out looking like my main spreadsheet. But it's only for a moment in time, an analysis for today on today's asset allocation. I bring in the current totals from the primary spreadsheet, then have separate columns for Cash, Stock, Bonds, etc. The Scottrade Brokerage may state $100,000 on the main spreadsheet, but on this spreadsheet the $100,000 is further separated into $60,000 in the Stock column and $40,000 in the Bond column. It's easy to see the asset allocation totals along with where the assets reside. That makes re-balancing easier.