Record keeping with Excel

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
jcheyman
Posts: 3
Joined: Sun Apr 12, 2015 4:26 pm

Record keeping with Excel

Post by 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
User avatar
cheese_breath
Posts: 10360
Joined: Wed Sep 14, 2011 7:08 pm

Re: Record keeping with Excel

Post by 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.
The surest way to know the future is when it becomes the past.
User avatar
dumbbunny
Posts: 907
Joined: Thu Apr 24, 2014 10:00 pm
Location: Oregon coast

Re: Record keeping with Excel

Post by dumbbunny »

“It’s the curse of old men to realize that in the end we control nothing." "Homeland" episode, "Gerontion"
User avatar
Doc
Posts: 9991
Joined: Sat Feb 24, 2007 1:10 pm
Location: Two left turns from Larry

Re: Record keeping with Excel

Post by Doc »

jcheyman wrote: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.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
CantPassAgain
Posts: 577
Joined: Fri Mar 15, 2013 8:49 pm

Re: Record keeping with Excel

Post by CantPassAgain »

Doc wrote:
jcheyman wrote: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.
User avatar
CABob
Posts: 5021
Joined: Sun Feb 25, 2007 8:55 pm
Location: Southern California

Re: Record keeping with Excel

Post by CABob »

CantPassAgain wrote:
Doc wrote:
jcheyman wrote: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.
Bob
cricket49
Posts: 269
Joined: Sun Apr 05, 2015 4:14 pm

Re: Record keeping with Excel

Post by cricket49 »

Doc wrote:
jcheyman wrote: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.
Expect the best. Prepare for the worst.
radix07
Posts: 10
Joined: Thu Sep 26, 2013 12:13 pm

Re: Record keeping with Excel

Post by 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.
User avatar
midareff
Posts: 7496
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Record keeping with Excel

Post by midareff »

CantPassAgain wrote: 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.
User avatar
midareff
Posts: 7496
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Record keeping with Excel

Post by midareff »

radix07 wrote: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
Posts: 10
Joined: Thu Sep 26, 2013 12:13 pm

Re: Record keeping with Excel

Post by radix07 »

midareff wrote: 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
Posts: 12571
Joined: Thu Apr 05, 2007 2:44 pm
Location: Delmarva Peninsula

Re: Record keeping with Excel

Post by jebmke »

radix07 wrote: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.
When you discover that you are riding a dead horse, the best strategy is to dismount.
radix07
Posts: 10
Joined: Thu Sep 26, 2013 12:13 pm

Re: Record keeping with Excel

Post by radix07 »

jebmke wrote: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...
Last edited by radix07 on Mon Apr 13, 2015 10:24 am, edited 1 time in total.
Bill M
Posts: 438
Joined: Sun Dec 30, 2012 10:10 pm

Re: Record keeping with Excel

Post by 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.
User avatar
bengal22
Posts: 1953
Joined: Sat Dec 03, 2011 6:20 pm
Location: Ohio

Re: Record keeping with Excel

Post by 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
"Earn All You Can; Give All You Can; Save All You Can." .... John Wesley
jebmke
Posts: 12571
Joined: Thu Apr 05, 2007 2:44 pm
Location: Delmarva Peninsula

Re: Record keeping with Excel

Post by 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.
When you discover that you are riding a dead horse, the best strategy is to dismount.
sharpjm
Posts: 657
Joined: Fri Feb 20, 2015 2:41 pm

Re: Record keeping with Excel

Post by 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
Posts: 7160
Joined: Sun Jun 28, 2009 2:21 pm

Re: Record keeping with Excel

Post by 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.
User avatar
midareff
Posts: 7496
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Record keeping with Excel

Post by midareff »

pshonore wrote: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.
User avatar
MN-Investor
Posts: 476
Joined: Mon May 02, 2011 9:04 am
Location: Twin Cities

Re: Record keeping with Excel

Post by 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.
The key to success - Save early, save often, invest well.
Post Reply