Technical Detail of Excel-Based Net Worth Report
A few while ago, I published a snapshot of my monthly personal finance dashboard, a complex view developed in Excel on top of Microsoft Money exports. Judging from the feedback I received, I believe it will be pretty useful for many. I apologize I never had time to showcase the technical details in the last few weeks. Let me share the details today.
The following explanations are based on the template file (525KB zipped), which should work for Microsoft Excel 2000 or higher versions. It demostrated how one can retrieve net worth information from Microsoft Money and organize such information in a user-defined format in Excel.
Some customization is needed to fit this template to your situation, so please plan at least 30 minutes if you want to build your own report. Believe me, it is worthwhile: even if you decide not to use the template afterwards, you can still learn some advanced Excel tricks.
Step 1: Create an account balance history report out of Microsoft Money. Each row should represent a unique account in your Money file, and each column should represent a unique month.
Step 2: Export the report to Microsoft Excel. If you have Microsoft Money and Microsoft Excel installed on the same machine, it is as easy as using "Export to Excel" option on the right-click menu from the Money report you just created. Your data should now be now displayed in Excel correctly.
Step 3: Copy to the Template. Open both the file that contains your financial data, and the template file. Copy all data from the export file to the "NW Raw" tab in the template file. (The shortcut will be CTRL-A to select all content in the first file, CTRL-C to copy, and CTRL-V on the "NW Raw" tab to paste.)
Step 4: Customize the mapping table on the "List" tab. This is the crucial step of the process. You will need to:
i. Clear the content of A23 to B62.
ii. Copy all account names to cells starting at A23.
iii. For each account in your Money file, specify which account category it should end up with. (For advanced users: account categories are defined in row 4-17, and you can define your own as appropriate.)
DONE! You can now enjoy the end result in the "Performance" tab!
P.S. Below are some more technical details of the implementation, in case you want to design your template from scratch:
"NW True" tab: It is a clone of your financial data on "NW Raw" with the exception of column A, which provides the categorization for each row. Note the formula in column A: VLOOKUP function helps to interpret the account name to a category name based on the mapping information in the "List" tab.
"Performance" tab: Note all formula between row 2 and row 14. SUMIF function is used to add up the balances of accounts in the same category. Especially, take a look at the treatment of row 9, which derives home equity from both "Home" and "Home Mortgage" categories.
I hope the same can be useful for Quicken users. If you successfully manage to get this implemented with Quicken, be sure to leave a comment!
Global Rich List is an interesting yet simple web site. It only serves one purpose: telling you how does your annual income stack up among all people in this world. Read
When it comes to Morningstar, many people will label it as the "mutual fund rating company." It is more than that now. Besides its flagship mutual fund rating system, today's Morningstar boasts independent equity research on 1,500+ stocks and tons of personal finance content. Read
A quick reminder: if you live in Southern states, it's your turn to receive the free annual credit report from the "big three" credit reporting agencies (CRAs): Equifax, Experian and TransUnion. You may access the official website at https://www.annualcreditreport.com/ starting from tomorrow to get access ... Read
What did Terri Schiavo remind us? Yes, the importance of living wills for sure, but there are more than that. In many families, mine included, there is a dominating party when it comes to personal finance. The de facto family CFO manages every personal finance ... Read
Hvae you tried Quicken, and if so is MM much better? Right now I just use Excel, partly becuase I'm a bit of a number junkie and like doing things myself but eventually as things get more intricate I will make the jump to one of those two.
Thank you for making this download available!
ceo: The blog author is a Microsoft employee, so using Quicken is probably against his enlightened self-interest. ;) Voice of one: I have used both Quicken and Money. I switched from Money three years ago because it wasn't quite where Quicken was in terms of automated data downloads from banks, etc. I believe that has changed however.
As to my experience with Quicken: I love it! It's really quite addictive if you find tracking your finances to be useful in your quest to build wealth.
Thanks for this. But where is the monthly dashboad report that you wrote about on May 3?
Why are some numbers green, others red and others black?
I have used both MM and Quicken. I switched to Mac, so now I use Quicken all the time (MM on Virtual PC was way too slow). I thought I didn't like the interface, but it is growing on me. The cash-flow forecasting isn't as good as in MM (not fine enough divisions and no interest forecast for savings accounts). However, the bill scheduling and Calendar work great. If I still had a PC I would probably stick to Money, but they are both really good programs, and (as Jamie said) totally addictive.
Thanks for helpful tool. I'm new to excel. Where did you imbed color coding for increase or decrease from previous month?
On the colors: red/green highlights the significant changes from last month. To change it, use Format | Conditional Formatting ... Good luck!
Why did NitroMed (NTMD) only increase 13% on news that it would begin sales of its drug BilDil by 7 July? Based on the market for Nitric Oxide drugs BilDil should bring sales of $220 million by 4Q 2005 and $550M by 3Q 2006. That would put NTMD's book value at $42/share. It is currently priced at $22/sh. Why is there such an unvaluation of this stock. Is it still undiscovered?
I used this template and was able to export data from Quicken into excel. The format is pretty much the same as you have it. However, depending on how your accounts are setup the template will need some tweaking. I just started and I think after about an hour or so I should have it setup for my use. If I make any enhances or something that might be worth sharing, I'll post it. Thanks MM!
