Automatic tracking of investment balances

[Rich_by_the_Bay]

Google spreadsheet has a function called GoogleFinance(). I buit a straightforward spreadsheet on Google so I can get to it from any computer. Aside from the usual spreadsheet capabilities, Here are some attributes you can retrieve:

View a list of attributes for mutual funds.

• closeYest: the NAV of a mutual fund.
• date: date at which NAV (net asset value) was reported.
• returnytd: year-to-date return total.
• netassets: The day-end net assets of the mutual fund. Net-asset figures are useful in gauging a fund's size, agility, and popularity. They help determine whether a small company fund, for example, can remain in its investment-objective category if its asset base reaches an ungainly size.
• change: change in NAV value between the most recent reported NAV, and the NAV prior to that.
• changepct: the % change in the NAV.
• yieldpct: Also known as the distribution yield, Morningstar computes this End Yield figure by summing the trailing 12-month's income distributions and dividing the sum by the last month's ending NAV, plus capital gains distributed over the same time period. Income refers only to interest payments from fixed-income securities and dividend payments from common stocks.
• returnday: one-day total return.
• return1: one-week total return.
• return4: four-week total return.
• return13: thirteen-week total return.
• return52: 52 week total return.
• return156: 156 week total return.
• return 260: 260 week total return.
• incomedividend: the amount of the most recent cash distribution for the fund.
• incomedividenddate: the date the above occurred.
• capitalgain: the amount of the most recent capital gain distribution from the fund.
• capitalgaindate: the date of the above.
• morningstarrating: the Morningstar "star" rating.
• expenseratio: The percentage of fund assets used to pay for operating expenses and management fees, including 12b-1 fees, administrative fees, and all other asset-based costs incurred by the fund, except brokerage costs. Fund expenses are reflected in the fund's NAV. Sales charges are not included in the expense ratio.

If this is more than you need, I second the vote for Vanguard or Yodlee with outside account retrieval. Personally, I track the big indexes generically on my google home page, but as for my personal investments I just check my Vanguard with outside accounts once a month or so (mostly to make sure my automatic deposits went through). Used to check more frequently but that got boring fast.

 

[d]

... Used to check more frequently but that got boring fast.

sometimes boring, sometimes exciting, sometimes frightening ...

 

[NW-Bound]

Just wondering, do most of you guys calculate your house equity as part of your net worth, and if you do, where do you get the information on how much the house is/should be worth? Zillow? I think it would be cool to braek down your entire net worth including net worth into the various "investments" you have, but was just wondering if anyone else ever does that.

I use Microsoft Money to keep track of my portfolio, which I admit is a bit messy: 2 IRAs + 2 Roths + 2 Coverdell + 2 401k + 2 I-bond accts (self, wife, and 2 children), 1 bank account, 2 brokerage accounts, 3 MM accounts.

MS Money takes care of stock and mutual fund prices, while I update interest from I-bonds and MMs at the end of the month. As for house equities, I do not pay as much attention as I do not plan to sell them anytime soon, and they are such illiquid assets that one cannot use price info for much. On the other hand, knowing whether to shift around between other assets can get me a bit more money.

I look at my total everyday, but do not trade daily (I would if I knew how to make it work). I've been through the Great Crash of 2000-2002, watching my portfolio getting decimated, so am used to up-and-down. Perhaps part of my taking it well is that I am still working part-time and not living off that portfolio. It would be scary to draw on a dwindling account. I guess I better not piss off the pointy heads (Dilbert-boss) at the big corp I am with.

 

[Free To Canoe]

Like many, I have several accounts at various places. Like "D" and others, I made a portfolio at Yahoo (for free) that updates daily. I copy it into my spreadsheet and that is it. I have added asset allocation and other things to the spreadsheet.

 

[grumpy]

How does the data from the first sheet (fund totals) get translated into the cells on the allocation sheet? Pasting entire fund totals into each allocation category would work if your funds were relatively "pure," but otherwise you'd need to break out each fund total into the various appropriate allocation buckets (e.g. Target Retirement 2025: X % to large cap, X% to small cap, X% to corp bonds, X% to REITS, etc). Do you do this?

Samclem,

I do have a balanced index fund (VBINX) that I split 60/40 (lg cap/fixed inc.) on the allocation sheet. Otherwise, I put each fund into a single allocation "bucket". While that is not totally accurate, it is close enough for my purposes. I keep my overall allocation to equities around 75% and am not too concerned about the split between small, mid, large or growth/value. We have been fully retired for four years now. Even with the current fall of the markets, our portfolio is still at approximately the same value as when we retired. During this period we bought a new house and a new car and have taken several nice vacations. Our withdrawal rate for regular expenses is well below 4% so I don't fiddle very much with our overall allocations.

 

[NW-Bound]

Samclem,

We have been fully retired for four years now. Even with the current fall of the markets, our portfolio is still at approximately the same value as when we retired. During this period we bought a new house and a new car and have taken several nice vacations. Our withdrawal rate for regular expenses is well below 4% so I don't fiddle very much with our overall allocations.

Well, I do not want to scare anybody, but can't help it...

Except for the difference that I am still working part-time (wife already ER), I also bought a 2nd home, a new car, and took several European and domestic vacations in the last 4 years (2-3 each year). And my children entered college too. Yet, my total net worth grows ... The reason was simple: We have been in a bull market since 2003.

But can it last? I sure hope it will, so that I can join my wife in ER and travel more. But until the smoke clears on the crude oil price, I am hunkering down a bit, but besides cancelling my annual Europe trip, don't know what else I could do, being frugal in all other areas.

I don't want to be a fear monger. Somebody, please tell me it's all hunky dory...

 

[Independent]

Sam, you're not the last to do this manually. I enter the balances once a quarter into an Excel spreadsheet. I then print a hard copy for my wife (it's not real to her if the numbers are on a computer screen). She does the same kind of "reasonableness" check on the numbers that I do. I like to be sure that I know why the total moved the way it did, and I also like to know that we aren't running out of money.

 

[Gotadimple]

You asked for it -- you got it!

Somebody, please tell me it's all hunky dory...

OK. It's hunky dory.

Happy days are here again
The skies above are clear again
Let us sing a song of cheer again
Happy days are here again
Altogether shout it now!
There's no one who can doubt it now
So let's tell the world about it now
Happy days are here again
Your cares and troubles are gone;
There'll be no more from now on
Happy days are here again
The skies above are clear again
Let us sing a song of cheer again
Happy days are here again


copyright 1929 Ager & Yellen

----------------------------
Sorry, I just couldn't help it!!!

-- Rita

 

[rs0460a]

If you don't have a strategy that requires you to adjust daily, why check? Over time I've worked from daily to weekly. Still working on going monthly instead.

Yodlee shows a overall net worth graph in monthly format for the last "x" months, so you can show your trend for a period that you select. In addition, it lists the actual net change data from the current/last month's data in detail.

- Ron

 

[NW-Bound]

ok. It's Hunky Dory.

Happy Days Are Here Again
The Skies Above Are Clear Again
Let Us Sing A Song Of Cheer Again
Happy Days Are Here Again
Altogether Shout It Now!
There's No One Who Can Doubt It Now
So Let's Tell The World About It Now
Happy Days Are Here Again
Your Cares And Troubles Are Gone;
There'll Be No More From Now On
Happy Days Are Here Again
The Skies Above Are Clear Again
Let Us Sing A Song Of Cheer Again
Happy Days Are Here Again


Copyright 1929 Ager & Yellen

----------------------------
Sorry, I Just Couldn't Help It!!!

-- Rita

Thanks, I Needed That!
I knew someone would come through for me!

 

[rogersteciak]

Just wondering, do most of you guys calculate your house equity as part of your net worth, and if you do, where do you get the information on how much the house is/should be worth? Zillow? I think it would be cool to braek down your entire net worth including net worth into the various "investments" you have, but was just wondering if anyone else ever does that.

I've been keeping track of my house value and including it in my net worth. I update the house value number once a year. I updated the mortgage balance number once a month when I got my statement.

I've been guestimating its value by starting with the appraisal from refinancing a mortgage many years ago and then adjusting this number based on statistics about how much the median house price has changed year-to-year for the city or county where I live. I get the statistics from press releases published by the California Association of Realtors, although I notice different real estate organizations publish slightly different numbers for the same periods of time.

I should consider Zillow as a source of information and might convert over to it. I don't take the value of my house too seriously on grounds that it doesn't generate cash income for me. It will generate capital gains income for me someday if I decide to relocate, but I will likely reinvest the after-tax money in another home somewhere else.

 

[rogersteciak]

I used to use Yodlee many years ago, but stopped using it when it changed its format and completely messed up my account.

Most of my portfolio is with a major Wall Street brokerage firm, while some of it is with a 401K plan and some with an HSA. Once a week, I visit each online account and copy and paste the account balances into my spreadsheet, overwriting the numbers that my spreadsheet calculates to project my balances into the future. I also fetch the end-of-month numbers for the final monthly numbers.

The main advantage of this weekly and monthly update cycle is that I keep current on my financial net worth. I can look at a graph that goes out many years into the future and figure out if I'm going to run out of money before I die. My spreadsheet assumes I will live to be 120, which is the maximum theoretical lifespan for humans.

 

[samclem]

IMy spreadsheet assumes I will live to be 120, which is the maximum theoretical lifespan for humans.

Not to pick a nit, but the maximum "theoretical" lifespan for humans is quite a bit older than 120.

There's at least one well-documented case of a French woman (Oldest people - Wikipedia, the free encyclopedia ) who lived to 122 years, 164 days. She would have run out of food 894 days early if she'd planed on only 120 years!

But, you point is still valid. Most of us are unlikely to see 120.

 

[rogersteciak]

Most of us are unlikely to see 120.

What leads me to use a high age as my postulated lifespan (for planning purposes) are the statistics that someone who reaches the age of 65 has a very good chance to living well into their 90s (which is referred to as "longevity risk" by the retirement professionals).

Even the IRS agrees and has published the required minimum distribution numbers for a traditional IRA (see Table 3 at the bottom of the page for tax publication 590 [Publication 590 (2007), Individual Retirement Arrangements (IRAs)]).

I saw a PBS program on the biotech revolution 10 years ago that said someone born a generation from now would have a life expectancy of 200 years (and be vibrant and healthy at that age also). I can only imagine the retirement planning challenges future generations will face if they have to live more than a century on their retirement income.

Unfortunately, most people alive today will not benefit much from biotech in terms of an increasing life span. But using a high number as one's postulated lifespan encourages one to set up a "perpetuity" that will maintain its inflation-adjusted purchasing power forever. Even though you can't benefit from having such a large amount of retirement assets, your heirs and favorite charities will certainly benefit.

 

[copyright1997reloaded]

It will bring you closer to your data -- whether you like it or not.

Interesting that you say that. I have a spreadsheet (excel) where I automatically update the quotes at end of day. (I used to have it set up to do realtime updates using dde but found that to be a bit of a hassle.) In any case, I have quite a few holdings, and one of the things I miss about the old days of updating manually is that I now feel less "connected" to how a security is acting.

 

[bigla]

I use Yahoo to keep my portfolio w/o any dollar or share amounts. I have all of that in Quicken. Every weekend I update quicken with the NAV's from the Yahoo portfolio and I get my balance. When times are good doing it every week is a pleasure. These, it just gets me upset.
Larry

 

[CitricAcid]

Interesting that you say that. I have a spreadsheet (excel) where I automatically update the quotes at end of day. (I used to have it set up to do realtime updates using dde but found that to be a bit of a hassle.) In any case, I have quite a few holdings, and one of the things I miss about the old days of updating manually is that I now feel less "connected" to how a security is acting.

Being connected to how your net worth isn't always a bad thing (or something you should miss). It is always fun during a bull market, but dreadful during a bear...

 

[pie2008]

I've been using Quicken since it first came out in the 1990's. For the past three or four years, I have been downloading nearly all of my transactions. I currently have about 30 different accounts (most are laddered CD's) and will probably have more in retirement. I track everything, so I always know my net worth.

Quicken has an excellent retirement planner built into it, and if you constantly update your accounts' information, that info is transferred automatically into your Retirement Planner. Both Quicken and their Retirement Planner require a bit of time to initially set up, but then updates are easy. They have a reminders feature that you can set up to monitor your accounts and alert you when CD's are about to mature, or when a bill is due.

I only buy an updated version about every 3 years -- I'm currently using Quicken Deluxe 2006, but plan to upgrade to Quicken Premier 2009 when it is released this fall. By the way, when a new verison is released, there are usually a few bugs still in the program and everyone complains about it. Then Quicken gets everything fixed over the following few weeks and it runs smoothly. Always keep checking for updates to ensure the software runs properly.

 

[ziggy29]

Happy days are here again
The skies above are clear again
Let us sing a song of cheer again
Happy days are here again

copyright 1929 Ager & Yellen

Uh-oh.

 

[cjking]

I user Excel spreadsheet to automatically retrieve price data for all stocks and funds I hold, from various websites that supply free data. The facilities in Excel 2007 for pulling in a table of data off a web page you browse to are very easy to use. I've also (re)created a copy of my spreadsheet in Google docs so I can access my data from everywhere.

I also automatically pull in things like interest rates and benchmark bond fund yields so I can calculated real benchmark yields for cash and bond asset classes.

By using Excel I can calculate things that Quicken doesn't show me, e.g. the predicted future return on different asset classes (according to my methodology) and hence the income my assets should support.

 

[pie2008]

Very interesting information about Excel, cjking. I'm still using Excel 2003 so I didn't know that Excel could do all of the things you mention. I did just get a laptop that has a trial version of Excel 2007 on it, but I haven't opened it yet. Can't wait to try it now!

 

[cjking]

I think Excel 2003 can extract data from the web as well, maybe not quite as user friendly. Look up "web query" in the online help. (I haven't actually used it - I went from 2000 to 2007 last year when I decided I need this facility.)

 

[pie2008]

I think Excel 2003 can extract data from the web as well, maybe not quite as user friendly. Look up "web query" in the online help. (I haven't actually used it - I went from 2000 to 2007 last year when I decided I need this facility.)

Thanks, cjking -- I'll look into this. I have just recently started studying the Excel tutorials. I was planning to more fully learn the program after retirement, but I see that I should spend some time learning it now because it might help with retirement planning. Thanks again.

 

[HFWR]

http://www.early-retirement.org/for...din-pull-live-stock-quotes-into-ss-31728.html

 

[RonBoyd]

I manually enter the numbers into Microsoft Money and check Money with Online Investing: Stocks, Personal Finance & Mutual Funds at SmartMoney.com (Select -- Subscription) and that with www.cakefinancial.com.

I am like W2R as I, too, enjoy the gut-wrenching experience of peeking daily -- I do it at night, however. (Don't want to ruin my day.)

In any event, I find Cake Financial to have the most accurate (eh, realistic) calculations. YMMV