My Personal Finance Journey

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

By Topics

Overall:
| Progress (86)
| Key Analysis (114)
| Resources (119)

My Finance:
| Credit (61)
| Insurance (11)
| Car & Home (21)
| Services (11)
| Savings (27)
| Tax (28)
| Others (31)

My Readings:
| Credit (46)
| Insurance (11)
| Car & Home (43)
| Services (5)
| Savings (21)
| Tax (44)
| Investments (72)
| Economy (16)
| Others (70)

My Portfolio:
| General (65)
| Transactions (44)
| Analysis (31)

Everything Else:
| About PFBlog (49)
| My Accounting (7)
| Misc (12)


MONTHLY ARCHIVE

Jan 2009 (5)
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)

HOT DEALS

Best Cashback Card:
Citi CashReturns(SM) MasterCard®
5% off EVERYTHING during introductory period. (review)

Best Sign-Up Bonus:
Citi Professional Card
$100 gift card after 1st purchase (review)

Best 0% APR Card:
Discover More Card
0% APR for purchase and balance transfer for 12 months (review)

Best Business Card:
Business Gold Rewards Card from American Express: Up to 40,000 bonus points in the 1st year (review)

Best Student Card:
Citi® mtvU™ Platinum Select® free music, gift cards and more (review)

Best Savings Account:
ING Direct Savings Account (4.30% APY)

Best Interest Checking:
Electric Orange from ING DIRECT. (Free Bill Pay. Earn up to 5.00% APY)

Free Stock Trading:
100% Free Stock Trading from Zecco.com

Free Credit Score:
Free Credit Report and Score from Experian



 

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!

More PFBlog Articles You Might Find Interesting ...


01/08/08 | Supercharge Your Frequent Flier Mileage Account by Applying for Co-Branded Credit Card | (3)
01/07/08 | How to Play the Dollar/Yuan Carry Trade | (9)
12/31/07 | Where to Park Your Cash in 2008 | (3)
12/04/07 | Free Stock Trades at Zecco.com | (10)
10/08/07 | What's Your Nest Egg Score? | (7)




Subscribe to Our RSS Feed:      Add to Google      Subscribe in Bloglines

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!



Read More ... 119 Posts In The Same Category


01/08/08 | Supercharge Your Frequent Flier Mileage Account by Applying for Co-Branded Credit Card | (3)
01/07/08 | How to Play the Dollar/Yuan Carry Trade | (9)
12/31/07 | Where to Park Your Cash in 2008 | (3)
12/04/07 | Free Stock Trades at Zecco.com | (10)
10/08/07 | What's Your Nest Egg Score? | (7)
09/13/07 | Citi CashReturns MasterCard - Credit Card Deal of the Year | (9)
08/14/07 | This Friday: Get Your Personal Finance Questions Answered By Professionals, For Free | (2)
08/09/07 | Free Domestic Calling For Life | (6)
01/25/07 | TiVo Is Entering China! | (14)
11/28/06 | Eloan Has More Money Than It Can Handle? | (2)
10/22/06 | Citibank's Online Lending Center Launched | (1)
10/21/06 | Credit Dispute Experience at Experian and Equifax | (6)
10/17/06 | American Express Blue Cash Now Offers Unlimited Rewards | (6)
10/11/06 | Bank of America Promotes Free Equity Trading For Savers | (8)
07/31/06 | Whitney Tilson: Applying Behavioral Finance to Value Investing
07/29/06 | EmigrantDirect Raised the Bar of High Yield Savings Account to 5.15% | (2)
04/29/06 | Personal Loans Made Easy | (13)
03/16/06 | Emigrant Direct Pays 4.50% APY for Your Savings, and Its New CD Offering | (12)
03/03/06 | The Cost of Keeping A U.S. Identity | (3)
11/26/05 | Self Employment 401(k) Brokerage Choices | (4)
11/21/05 | Form Your Small Business Nice and Easy | (4)
07/21/05 | How Rich Are You? | (1)
06/30/05 | Great Investing Education From Morningstar
05/31/05 | Reminder: Free Credit Report Rolled Out To Southern States On June 1st
05/25/05 | If Something Happens To Me | (5)
04/08/05 | VirtualBank Fought Back, Yielding 3.05% Now | (3)
04/05/05 | Treasury Rewrites Series EE Savings Bonds Rules
04/05/05 | ING Direct at 3.00% Now
03/22/05 | ING Direct and VirtualBank Both at 2.80% APY. Who's Next? | (3)
03/13/05 | Emigrant Direct Savings Account Now Yields 3.25% | (15)
03/05/05 | What's Your Life Expectancy?
03/03/05 | Bankrate.com Makes Adjustable Rate Mortgage Search More Powerful
02/07/05 | PFBlog Product Reviews | (2)
02/02/05 | Amazon Prime: All-You-Can-Eat Express Shipping
01/28/05 | EmigrantDirect American Dream Savings Account Yields 3.0% | (3)
01/18/05 | More Free Tax Filing Options This Year | (3)
01/12/05 | DeductionPro: Free for Download | (1)
12/25/04 | Want To Buy A TiVo? Get $20 Here
12/25/04 | TiVo: Cannot Be More Satisfied | (3)
12/23/04 | More Personal Finance Bloggers | (3)
12/07/04 | Rip-Off Report
11/19/04 | Heated War of Saving Rates | (2)
11/05/04 | Higher Saving Rate from ING Direct and CapitalOne | (4)
11/04/04 | New Savings Bond Rates: EE = 3.25%, I = 3.67% | (2)
10/02/04 | Vonage: Much Improved VOIP Technology (And Referral Deal) | (5)
09/29/04 | My Fellow Bloggers | (3)
09/28/04 | The Ultimate Real Estate Search Engine
09/20/04 | First Impression of Microsoft Money 2005 | (6)
09/02/04 | Home Price Check, Again
08/18/04 | 66 Ways to Save Money
08/17/04 | Salary Timer | (1)
08/09/04 | MyRatePlan, A Good Data Compiler
07/28/04 | A Closer Look at 2004 Sales-Tax-Free Holidays
07/28/04 | 0% Offers
07/18/04 | CapitalOne No Regrets Certificate of Deposit
07/09/04 | "Profit from Chase" Credit Card
07/05/04 | Free Credit Report from Experian
07/02/04 | AuctionDrop Helps You to Sell at eBay
06/23/04 | Bill Me Later®
06/18/04 | True Cost to Own A Car
06/03/04 | The Kirk Report
05/31/04 | DebtSmart.com - A Great Personal Finance Site
05/25/04 | Individual Tax Statistics by Zip Code
05/20/04 | $175 Promotion from E*Trade Bank | (2)
05/03/04 | New Saving Bonds Rates: EE = 2.84%, I = 3.39%
05/02/04 | King County, WA's eSales Search System
04/30/04 | J.D. Power 2004 Vehicle Initial Quality Survey
04/30/04 | Refinancing Closed, Part III: LendingTree vs BankRate
04/25/04 | Ditech Free eApparaisal Service
04/15/04 | AmeriTrade's New Promotion
04/15/04 | Fidelity MasterCard® Credit Cards | (1)
04/08/04 | 2004 Stock Market Holiday Schedule
04/02/04 | Medved QuoteTracker
03/18/04 | Insider Activity Tearsheet
03/09/04 | Gas Price Tracker
03/07/04 | Intelliscope Broadens My Access to Research Reports
03/05/04 | Experian National Score Index
02/29/04 | Occupational Outlook Handbook, 2004-05 Edition
02/21/04 | Providian Real Rewards
02/21/04 | SmartMoney Stock Screens
02/18/04 | Visa Extras
02/12/04 | Excel Add-In: MSN Money Stock Quotes
02/04/04 | ExactChoice.com - Buying Guide for Computers | (3)
01/31/04 | This Year, Everyone Can File Tax For Free!
01/24/04 | DJ30 and S&P 500 P/E Ratio
01/11/04 | Free Magazines from Northwest Airlines (CFI)
12/31/03 | Finding Cheap Magazine Subscriptions | (1)
12/29/03 | AuctionSniper.com Wins You EBay Bids
12/19/03 | Short the NASDAQ
12/15/03 | Exchange Gift Cards at CertificateSwap.com | (3)
12/12/03 | The Ultimate Account Aggregator
12/11/03 | New Building Permits Report
12/11/03 | "Guru Analysis" from NASDAQ.com
12/10/03 | Comparison of Credit Card Sites
12/09/03 | FreeTrade.com
12/08/03 | Inflation-Protected CDs
12/05/03 | iShip.com Helps to Reduce Shipping Cost
11/30/03 | Broadband Phone Service: Vonage and Others | (2)
11/28/03 | Schwab Equity Snapshot Report Offers Little Value
11/28/03 | Selling Mileage for Cash | (1)
11/18/03 | How to Benefit from Weak Dollar?
11/17/03 | "Real Estate IRA" | (2)
11/14/03 | The New Inflation Hedges
11/08/03 | Discussion Board at Morningstar.com | (1)
11/03/03 | Clubphoto.com: Best-Value Online Photo Printing
11/01/03 | 2.75% Interest Checking Account at Presidential | (2)
11/01/03 | Financial Risk Manager (FRM) E-Learning Site
10/28/03 | FICO Score Estimator
10/19/03 | Comparison-Shopping Tools Lure Travelers
10/07/03 | Saving Bonds Direct
10/04/03 | Upromise.com
10/01/03 | ShareBuilder Visa Platinum Card | (1)
09/25/03 | Cheap Long Distance Service | (2)
09/24/03 | Eloan.com Credit Monitoring Service
09/16/03 | Benefit from Promotions | (3)
09/14/03 | Stock Research Resources
08/22/03 | Cheap Gas - Arco and Safeway | (1)
07/29/03 | My Top 10 Favorite (Free) Personal Finance Sites | (1)

Recommended Offers


•  Chase Home Improvement Rewards(SM) Card
•  Citi® Platinum Select® / AAdvantage® World MasterCard®
•  The Platinum Business Credit Card
•  Citi® PremierPass(SM) Card
•  British Airways Visa® Signature Card
•  The Knot Credit Card from American Express®
•  WorldPerks® Visa® Signature Card
•  Account Nowâ„¢ Vantage Debit MasterCard®
•  Continental Airlines Business MasterCard®
•  Business Green Rewards Card








This page was last rebuilt at December 30, 2009 08:07 PM PST.
  

RSS FEED



PREMIUM SPONSORS

Payday Loans
Payday Loan
Injury Claims
Bad Credit Loans - Free Quote


SITE REVIEWS

Low Cost Loans
Cheap Loans
Bad Credit Loans


PERSONAL FINANCE BLOGS I READ

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




SELECTED POSTS

Credit Cards:

• Best 0% APR Intro Offers

• Citi CashReturns MasterCard

• Citi Payment Partner Program

• American Express Blue Cash

• Free Credit Report

Banking:

• Emigrant Direct Savings Account

Investing:

• Opening A Self-Employment 401(k) Account

Real Estate:

• Most Affordable Housing Markets

Insurance:

• Auto Insurance Saving Tips

• Vehicle Insurance Ratings

Other Services:

• ooma - free domestic calling for life

.

Featured Sites


• Find a fast payday loan in the UK. Even if you have a bad credit history, arrears or CCJ's, Loansite's top lenders can help you compare loans and find a low APR secured loan, homeowner loan or tenant loan with fast approval in principle.

• Better Trades takes the fear out of buying and selling options.

• The Internet's most trusted credit card resource directory.

• Bad Credit Auto Loans are loans for people who have a poor credit history and cannot qualify for an auto loan. If you are able to secure a bad credit auto loan, you should consider it to be a second chance to improve your credit rating by making all auto loan payments on time.

• Student Loans - Confused about student loans? Have a professional education finance advisor assist you with all of your federal and private student loan questions. Also find free college financing tools and resources at NextStudent.com.

• Have a Personal Bankruptcy Lawyer assist with filing bankruptcy for a chapter 13 or chapter 7 bankruptcy case. Free bankruptcy evaluation tool.

• Bad Credit Loans - find out some tips about improving your credit score and choosing the best bad credit lender.

• Legal Helpers experienced bankruptcy lawyers can help you out of debt. Take advantage of our Free debt consultation. Our bankruptcy lawyers will help you understand your best option to eliminate your debt. Ask about our 100% guarantee.

• MM Money is an interesting money blogthat covers many aspects of personal financial matters.

• This insurance weblog covers all major types of insurance, including health, life and car insurance

• Bestcardguide.com offers independent review of best credit cards

• Real Bankruptcy offers excellent tips of bankruptcy and how to avoid them

• Small Business Loans

• Credit Card Debt Relief

• Online Credit Card Processing

• Stock Investments

• Bad Credit Auto Loans - washington state's bad credit auto financing leader.

• fha loan - Hud approved lender, FHA Home Loan Services provides 1st time home buyer loans & fixed rate mortgage refinance options for borrowers with bad credit.

• Credit Repair
repairmycreditnow.com/Credit_Repair_Advice.htm - repair my credit now credit services offers credit repair, restoration and education through our exclusive "v" phase process â„¢


Want to See Your Ads Here? Claim Your Ad Space at Personal Finance Weblog by Writing to Webmaster. Starting at $49/month, see your text ads in thousands of indexed pages at PFBlog, the Best Personal Finance Blog. Full Rate Card Available Here.

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