Gyroscopic Investing

Clive wrote:
=((end_amount/start_amount)^(1/number_of_years))-1

is pretty generic

Doh!

Thanks.

i don't have anything to contribute to this thread...  but i do want to say thanks...  i have been tracking my investments using a combination of the information vanguard provides and my "best guess".
In the last couple months  i have been begun to study spread sheets so i can start tracking my investments closely, these spreadsheet threads that have been popping up are saving me a lot of hours trying to find this information in the on-line tutorials that i have been looking at...   

Clive wrote:

rickb wrote: Anyone know how to compute CAGR (given a starting and ending value, and number of years) using google docs?  It looks like rate() should do it, but I haven't been able to get it to work.  I'm looking for the equivalent of what in openoffice would be rri(years;start;end).

=((end_amount/start_amount)^(1/number_of_years))-1

is pretty generic

That only works for a one-time purchase - that is if there were no additional investments after the start_amount had been invested. I guess this is what you were looking for.

Wow, you guys were right about Google docs.  Thanks so much for all these ideas.  I was able to whip up a quick tracker just over my lunch hour!  Didn't expect it to actually be that easy.  It's nice to have it laid out so that at a glance you can see all of your percentages, how many $ out of perfect 4x25 balance an asset is, etc.

If anyone is interested in how to include the spot price of gold in a cell in order to easily price their coins, the following formula will scrape the gold price off of APMEX:
Here's the formula for silver, too, for any of you that might add a little cream to their Permanent Portfolio coffee:
I didn't come up with the above myself.  This thread on google was my source.

The one thing I still don't have very well automated is pricing my individual Treasury securities (such as 30-year Treasury Bonds or the 1- to 3-year Treasury Notes that I use as "cash".)  Does anyone have any suggestions on how they've accomplished this?

If I was a real fancy pants, I'd get the spreadhsheet to calculate the value of my savings bonds as well.  Then I just teach google docs how to execute a trade and I may not be doing anything to my PP until I retire.  

Treasuries is a bit tricky.  I've used two methods.  

The old way:

• On an arbitrary day, lookup price of TLT. For the same day, find out the price of each of my 30 year treasuries
• Create spreadsheet entries for each 30 year treasury that adjusts their current value according to the amount TLT has changed

This approach has the benefit of simplicity, but several problems:

• TLT maturity and the maturity of my treasuries is different, so interest rate changes will cause proportionally different changes to valuation
• Average maturity of TLT doesn't change, maturity of each individual treasury I hold does (it goes down, day by day), causing yet more distortion

The new way:

• Get current 30 year treasury yields - I use Yahoo for this: =index(importhtml("http://finance.yahoo.com/bonds", "table",K73),8,2).
• Use the =Price() function in GoogleSpreadsheets to calculate the actual price of my bonds, based on their coupon and maturity. Here's a link to a Google Spreadsheet that anyone should be able to view that shows how I do this - I only included 02/19/2039 Treasuries, but it should be obvious how to do different dates: https://spreadsheets.google.com/ccc?key ... y=CL_6pMsD

I am currently using the new way and pretty happy with it.  It does not exactly align with what Vanguard tells me each bond is worth, but it is close enough, and is automated, which I really like.

I cannot take credit for the use of Price() for calculating Treasury values - someone on Bogleheads suggested it.  My contribution was combining it with the current bond prices of Yahoo to automate it.

What are ppl using to track the PP that takes into accnt dividends from TLT & VTI when calculating performance. Google finance just adds the dividend to cash but does not use it to calculate performance

julian wrote: What are ppl using to track the PP that takes into accnt dividends from TLT & VTI when calculating performance. Google finance just adds the dividend to cash but does not use it to calculate performance

Kapitall.com and SmartMoney.com do a good job with reinvesting dividends as they happen. But those sites have some minor issues with historical dividend calculating. SmartMoney.com has the only real-time portfolio chart that I'm aware of. It's pretty amazing to see the PP remain stable, every minute of the day, in real time.

julian wrote: What are ppl using to track the PP that takes into accnt dividends from TLT & VTI when calculating performance. Google finance just adds the dividend to cash but does not use it to calculate performance

I just manually add new entries to my spreadsheet of all PP-related transactions, once a month or so.  This takes less than 10 minutes and satisfies my obsessive-compulsive need to know exactly how well the PP is working for me.

fnord123 wrote: I cannot take credit for the use of Price() for calculating Treasury values - someone on Bogleheads suggested it.  My contribution was combining it with the current bond prices of Yahoo to automate it.

A belated thanks for laying this out.  I never could get the prices to come out quite right to match what Fidelity was saying but it's still neat to be able to do it.

Now to just convince all of these financial sites to never, ever move their HTML tables around in any way. 

Just want to add that I have a Bloomberg machine and the smart money portfolio blows it a way & you can see it on ur phone!

for anyone who is interested.... for open-office you can get an extension called "get quote" http://getquote-tedsoft.blogspot.com/
that will grab stock quotes from yahoo finance.  the cell would be =GETQUOTE("SHY") or =GETQUOTE("A1") (if cell A1 was SHY or some other stock symbol)

O...M...G... for those of you trying not to check your PP too often - Do not, I repeat, DO NOT setup a SmartMoney.com portfolio and then proceed to go to that page while the market is open...

Watching realtime gain/loss in dollars is like crack if you are a compulsive portfolio checker like I am.  This morning I spent a good 10 minutes just staring at the $ counter like a gambling addict watching a slot machine payout... it is bad.

Storm wrote: Watching realtime gain/loss in dollars is like crack if you are a compulsive portfolio checker like I am.  This morning I spent a good 10 minutes just staring at the $ counter like a gambling addict watching a slot machine payout... it is bad.

Ha ha, so very true.  It's really nicely presented and just so... fun to watch.  I only allow myself to check on the PP once per month but when I do, I always like to take at least a few seconds to watch those cool charts.

I also have several "play" portfolios on there that I allow myself to check whenever I want.  For example, I put together a "2x leveraged" version of the PP based on Wonk's formulation for a little entertainment value.  (Note that I do not invest in a leveraged PP with one red cent of real money!)

Gumby wrote: I avoid looking at my portfolio specifics as much as I can, but once in awhile I'm curious to see which asset is holding up the portfolio at any given time. I just bookmark the following URL:

http://www.google.com/finance/chart?cht ... hy&tlf=12h

and that gives me a quick real-time peek at the PP — if I want to take its pulse.

It looks like Google has updated the way it calls URLs. They seem to be locking out multiple stock comparisons in the same static URL-based chart.

As a replacement for the URL described above, you can use Yahoo to peek at the short term asset comparisons. Here is a simple example of how you build the chart in your browser:

http://chart.finance.yahoo.com/z?s=TLT& ... &c=GLD,VTI

...which gives you the following real-time chart:

[align=center][/align]

To customize the chart, use these query string parameters in the URL:

z=[size]    (s m l)
t=[time]    (1d 5d 1m 3m 6m 1y 2y 5y my)
c=[ticker]  (GLD TLT VTI)
q=[type]    (bar line candle)

Gumby,

Thanks for this!  I really miss the Google version of the chart you brought to our attention.  That was pure gold!

The yahoo version, although not as "clean", will have to do.

I preferred the Google one as well (it fit neatly into a corner of my Mac's Dashboard). I'll keep my eye on their URL patterns to see if it will ever be possible again.

Clive wrote: Sometimes individual tickers seem to cause a no chart return for a day or two.

The Canadian set

http://www.google.com/finance/chart?cht ... SB&tlf=12h



and excluding both TLT and GLD

http://www.google.com/finance/chart?cht ... HY&tlf=12h



still work.

Nope. The second chart no longer works for me, here in the US (at least not today). We just see a "No Chart Available" on that second image. Hopefully it's temporary.

Clive wrote: I see all four of these OK (excepting TLO just having price data up to 1pm) http://www.google.com/finance/chart?cht ... hy&tlf=12h



and suspect the others will all come back again in a day or so

It seems to be working again. Thanks, Clive!

Gumby wrote: I avoid looking at my portfolio specifics as much as I can, but once in awhile I'm curious to see which asset is holding up the portfolio at any given time. I just bookmark the following URL:

http://www.google.com/finance/chart?cht ... hy&tlf=12h

and that gives me a quick real-time peek at the PP — if I want to take its pulse.

It looks like this:



(VTI is red, GLD is yellow, TLT is blue and SHY is green).

I find that occasionally glancing at those squiggly lines allows me to go a few weeks without checking my actual portfolio numbers. It's a bit like peeking at the drizzling code of the Matrix. And by only glancing at this URL I avoid looking at financial websites and their meaningless time-consuming articles. It's still a little addictive though, but at least it allows me to be more detached from my detailed PP performance.

Hint: You can change that URL to pretty much any array of securities — you just have to remember which color corresponds to each security.

This is beautiful. Even though I spent a lot of time creating google doc spreadsheets, I'm going to try to pull away from them for 29 days a month and just look at this.

Which can you recommend for non-US residents investing in the US market?

This is just of historical interest, but many years ago I wrote a program to do that for the TRS-80, and HB even mentioned it in his newsletter. I don't think I sold any copies, though.