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!