My Personal Finance Journey

Personal finance observation, musing and decisions in a journey toward financial independence by 2020 with at least $3 million.


By Topics

Overall:
0. About (10)
1. My Progress (139)
2. Car & Home (107)
3. Credit (138)
4. Banking (33)
5. Saving (49)
6. Investing (308)
7. Taxes (89)
8. Spending (74)
9. Misc (97)
A. Archive (49)



MONTHLY ARCHIVE

Feb 2014 (3)
Jan 2014 (6)
Jan 2012 (1)
Apr 2011 (1)
Mar 2011 (1)
Feb 2011 (1)
Jan 2011 (1)
Dec 2010 (1)
Oct 2010 (1)
Sep 2010 (1)
Aug 2010 (1)
Jul 2010 (1)
Jun 2010 (1)
May 2010 (1)
Apr 2010 (1)
Mar 2010 (6)
Feb 2010 (2)
Jan 2010 (7)
Dec 2009 (3)
Feb 2009 (4)
Jan 2009 (8)
Dec 2008 (1)
Jun 2008 (2)
May 2008 (2)
Apr 2008 (5)
Feb 2008 (3)
Jan 2008 (15)
Dec 2007 (32)
Nov 2007 (6)
Oct 2007 (8)
Sep 2007 (9)
Aug 2007 (24)
Jul 2007 (2)
Jun 2007 (1)
May 2007 (3)
Apr 2007 (4)
Mar 2007 (4)
Feb 2007 (13)
Jan 2007 (6)
Dec 2006 (3)
Nov 2006 (7)
Oct 2006 (7)
Sep 2006 (6)
Aug 2006 (4)
Jul 2006 (10)
Jun 2006 (1)
May 2006 (3)
Apr 2006 (2)
Mar 2006 (6)
Feb 2006 (6)
Jan 2006 (3)
Dec 2005 (1)
Nov 2005 (9)
Oct 2005 (8)
Sep 2005 (13)
Aug 2005 (25)
Jul 2005 (16)
Jun 2005 (17)
May 2005 (19)
Apr 2005 (20)
Mar 2005 (24)
Feb 2005 (23)
Jan 2005 (36)
Dec 2004 (40)
Nov 2004 (34)
Oct 2004 (17)
Sep 2004 (21)
Aug 2004 (59)
Jul 2004 (37)
Jun 2004 (31)
May 2004 (29)
Apr 2004 (52)
Mar 2004 (49)
Feb 2004 (49)
Jan 2004 (31)
Dec 2003 (48)
Nov 2003 (52)
Oct 2003 (29)
Sep 2003 (8)
Aug 2003 (5)
Jul 2003 (2)
Jun 2003 (2)
May 2003 (5)
Apr 2003 (2)
Mar 2003 (2)
Feb 2003 (3)
Jan 2003 (29)



 

Technical Detail of Excel-Based Net Worth Report

Contributed by mm | June 28, 2005 12:45 PM PST

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.

ExcelSample1.GIF

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!

This Post Has Received 10 Comments. Share Your Opinions Too.


ceo Commented on June 28, 2005

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.


Laura Commented on June 28, 2005

Thank you for making this download available!


Jamie Commented on June 28, 2005

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.


John Commented on June 30, 2005

Thanks for this. But where is the monthly dashboad report that you wrote about on May 3?


JC Commented on June 30, 2005

Why are some numbers green, others red and others black?


Theresa Commented on June 30, 2005

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.


Dallyn Commented on July 1, 2005

Thanks for helpful tool. I'm new to excel. Where did you imbed color coding for increase or decrease from previous month?


mm Commented on July 1, 2005

On the colors: red/green highlights the significant changes from last month. To change it, use Format | Conditional Formatting ... Good luck!


Bioguy Commented on July 2, 2005

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?


Jman Commented on July 8, 2005

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!


Add Your Comments










Remember personal information?




(It will take a few moments for your comment to be published. Please do not close the window until then.)


Read More ... 139 Posts In The Same Category










This page was last rebuilt at February 09, 2014 08:21 AM PST. (536 Words)
 

RSS FEED





PERSONAL FINANCE BLOGS I READ

Consumerism Commentary
Get Rich Slowly
My Money Blog
All Financial Matters
The Simple Dollar






.



Copyright 2003-2014, PFBlog.com. All Rights Reserved. (Privacy Policy)