Spreadsheet for backtesting (includes TrevH's data)

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.

Postby gummy » Tue May 29, 2007 2:30 pm

Mamma mia!
Simba, you is so fast!
I wuz still workin' on rev5j when you done finished rev5k with the probability stuff included. :D

About Ito, the lognormal distribution of prices at time T (starting with price Po) is:
Image
I just stuck in the parameters r, s and Po, using your data.
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O

Postby jms969 » Tue May 29, 2007 2:55 pm

gummy wrote:Mamma mia!
Simba, you is so fast!
I wuz still workin' on rev5j when you done finished rev5k with the probability stuff included. :D

About Ito, the lognormal distribution of prices at time T (starting with price Po) is:
Image
I just stuck in the parameters r, s and Po, using your data.


WOW I do love having a PhD in mathmatics on the board. My little engineering background just does not hold a candle to you :lol:
I want to die peacefully in my sleep like my grandfather, not screaming like the passengers in his car.
jms969
 
Posts: 156
Joined: Tue Feb 20, 2007 5:56 pm
Location: South Central Wisconsin

Re: updated with gummy's changes

Postby jms969 » Tue May 29, 2007 2:58 pm

simba wrote:jms - I updated the spreadsheet with gummy's changes.

Latest version rev5k can be downloaded here

Honestly I can't say I understand everything he did there. I am still reading about Ito's calculus and sortino ratio.


Yeah Gummy gets out ahead of me pretty quickly as well (but that is very good!!!). But what an amazing group of people here!!! I learn more here than I ever dreamed.


The following site is Aswath Damodaran's, Professor of Finance and David Margolis Teaching Fellow at the Stern School of Business at New York University. He has an amazing array of spreadsheets and business data that is available for your use. It is free and regularly updated.

Enjoy...

http://pages.stern.nyu.edu/~adamodar/New_Home_Page/

Peace,

JMS
I want to die peacefully in my sleep like my grandfather, not screaming like the passengers in his car.
jms969
 
Posts: 156
Joined: Tue Feb 20, 2007 5:56 pm
Location: South Central Wisconsin

Postby gummy » Tue May 29, 2007 3:32 pm

jms969:
Thanks for the link to all them thar spreadsheets.
I reckon I gotta steal some things to add to this collection:**
http://www.gummy-stuff.org/stock-charts.htm

** Thievery is my favourite hobby ... :lol:
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O

Postby jms969 » Tue May 29, 2007 3:44 pm

gummy wrote:jms969:
Thanks for the link to all them thar spreadsheets.
I reckon I gotta steal some things to add to this collection:**
http://www.gummy-stuff.org/stock-charts.htm

** Thievery is my favourite hobby ... :lol:


Great collection of charts!!!

We are one sick bunch :lol:
I want to die peacefully in my sleep like my grandfather, not screaming like the passengers in his car.
jms969
 
Posts: 156
Joined: Tue Feb 20, 2007 5:56 pm
Location: South Central Wisconsin

I think this illustrates..

Postby edge » Tue May 29, 2007 3:56 pm

I think the past few exchanges illustrate the need for a collaborative tool to help streamline updates and availability - google spreadsheets could work here. If any more assistance is required to understand how to set this up I could volunteer to help.

In any case, the changes are pretty cool.
edge
 
Posts: 1881
Joined: Mon Feb 19, 2007 7:44 pm
Location: Great Falls VA

Re: I think this illustrates..

Postby jms969 » Tue May 29, 2007 4:35 pm

edge wrote:I think the past few exchanges illustrate the need for a collaborative tool to help streamline updates and availability - google spreadsheets could work here. If any more assistance is required to understand how to set this up I could volunteer to help.

In any case, the changes are pretty cool.


Agreed!!!

But one person, Simba, (or small committee) still needs ultimate say over what is and is not accepted for the release version... We also need to reduce the load on Simba...

...and we of course need to be able to suck the release version off of google ss into excel.
Last edited by jms969 on Tue May 29, 2007 4:49 pm, edited 1 time in total.
I want to die peacefully in my sleep like my grandfather, not screaming like the passengers in his car.
jms969
 
Posts: 156
Joined: Tue Feb 20, 2007 5:56 pm
Location: South Central Wisconsin

Postby gummy » Tue May 29, 2007 4:46 pm

But one person, Simba, (or small committee) still needs ultimate say over what is and is not accepted for the release version...
Agreed! Now if we can only reduce the pressure (and sweat) on Simba.

P.S.
The google spreadsheets can't handle all of Excel's functionality ... but it's cheeep, eh?

If Simba agrees, I can collect revisions, stick them on my website then PM Simba so he can check them out and decide it they're acceptable.
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O

Postby norm » Tue May 29, 2007 5:30 pm

gummy wrote:
After I entered the % of my allocations nothing appeared on the graphs. Am I missing a step?
Do your allocations add to 100%?



Yes they do. In addition on Row 45 under Total it shows Error and under CAGR it shows #VALUE!.
norm
 
Posts: 594
Joined: Mon Feb 19, 2007 7:10 pm

Postby edge » Tue May 29, 2007 5:41 pm

gummy wrote:
But one person, Simba, (or small committee) still needs ultimate say over what is and is not accepted for the release version...
Agreed! Now if we can only reduce the pressure (and sweat) on Simba.

P.S.
The google spreadsheets can't handle all of Excel's functionality ... but it's cheeep, eh?

If Simba agrees, I can collect revisions, stick them on my website then PM Simba so he can check them out and decide it they're acceptable.


The permissions/sharing policy available in the Google SS should support the kind of collaboration that JMS was talking about. Gummy's point needs addressing - I think the lack of functionality may have more to do with the fact that the product is very new and definitely still beta. The graphing capability is still very beta in terms of features.

Which critical features are missing (Gummy)?
edge
 
Posts: 1881
Joined: Mon Feb 19, 2007 7:44 pm
Location: Great Falls VA

Gummy/Simba

Postby SoonerSunDevil » Tue May 29, 2007 5:42 pm

Gummy/Simba,

Could either of you explain what the graph showing the "Probability of achieving less than 1000$P in 15 years" means?

I'm sorry that my mathematical abilities are limited; I only had Calculus I and II during college :D

Thanks,

John
User avatar
SoonerSunDevil
 
Posts: 2001
Joined: Mon Feb 19, 2007 10:32 pm
Location: The desert

Spreadsheet updated to include Sortino Ratio

Postby simba » Tue May 29, 2007 5:50 pm

Thanks to Gummy - I updated the spreadsheet to calculate Sortino ratio.

The formula I used is

=((AVERAGE(AB5:AB39))-(AVERAGE(Data_72_06!$V$5:$V$39)))/STDEV(IF(AB5:AB39<AVERAGE(Data_72_06!$V$5:$V$39),AB5:AB39))

Need to enter that formula with Ctrl-Shift-enter.

Latest version rev5m can be downloaded here
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 7:47 pm

Postby sterjs » Tue May 29, 2007 6:39 pm

I posted a link to the sheet on another messageboard and one poster thinks that the sheet's calculation of Sharpe Ratios is off:

"yea the sharpe ratio calculations don't take compounding into effect

they use Average(portfolio returns)-Average(tbill returns) to get the excess returns. it should be subtracting the tbill return from the portfolio return in each time period and then calculating the compound returns.

their sharpe ratios are historical underestimates in this case (sheets SP-72-06 and SP-85-06). "
sterjs
 
Posts: 281
Joined: Sun Mar 25, 2007 2:22 am

Postby norm » Tue May 29, 2007 6:50 pm

Simba,

After I entered the % of my allocations nothing appeared on the graphs. Am I missing a step? My total allocation comes to 100%
norm
 
Posts: 594
Joined: Mon Feb 19, 2007 7:10 pm

Postby gummy » Tue May 29, 2007 8:53 pm

Could either of you explain what the graph showing the "Probability of achieving less than 1000$P in 15 years" means?

The magic formula I gave above, namely
Image
uses the historical mean return r and standard deviation s and the initial portfolio value Po.

The spreadsheet calculates the probability (based upon these parameters) that, in T years, your portfolio will be less than P.
Actually, the horizontal axis is in units of $1K so, for the example shown in the left chart,
there's a 50% probability that your portfolio will be less than $100K in T = 15 years.

Change the allocation and/or the time and get different probabilities.
That's assuming you believe in Ito's magic equation. :)

After I entered the % of my allocations nothing appeared on the graphs. Am I missing a step? My total allocation comes to 100%

To relieve Simba I'll take a try at explaining. Just email the "faulty" spreadsheet to:
pjponzo@golden.net

the sharpe ratio calculations don't take compounding into effect ... they use Average(portfolio returns)-Average(tbill returns) to get the excess returns. it should be

That's the "standard" definition. Indeed, the numerator is the "Expected" excess which is the same as the Average excess which is the same as the difference in the two Averages.
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O

Error in Returns_85_06 asset allocation links; change plans

Postby Eric White » Wed May 30, 2007 1:20 am

Simba:

ERROR FOR 85-06 CALCULATIONS:

Cell Z3 on sheet Returns_85_06 uses the following formula:
=Portfolio!$K$28

Cell AA3 on sheet Returns_85_06 uses the following formula:
=Portfolio!$K$30

It looks like Cell AA3 needs to use the formula =Portfolio!$K$29, and the remaining cells in the row need new transposed links.

ERROR CHECKING REQUEST
Also, if you can eliminate this error check in the calculations and instead use simple 100% summary visual sanity checks, that reduces rework for each rev to enable efficient frontier Excel Solver calculations. I have redone the Solver work twice now and it's been obsoleted a third time. I will not be supporting efficient frontier additions moving forward if we can't find a solution that will be incorporated or planned with compatibility in mind.

CHANGE PLANNING
Although I'm Pi Tau Sigma and Tau Beta Pi and enjoy mathematical acrobatics, simplicity is our friend until the basic portfolio options and datasets get nailed down. It will let us focus on data quality and basic calculation integrity. I think after the portfolio request additions and resulting structures settle down we should initiate discussions (and polls!) around metrics that are most useful to Diehards overall. I'm a little worried that as we add metrics into the tool, we may start to lose the forest for the trees.

Of course, I'm a guilty party in this process as well, shooting for the efficient frontier calcs too early :| Portfolio Process Capability (Cp), Kurtosis, Monte Carlo analysis, discrete event simulations, and system dynamics models should all have their day in the sun, just maybe not yet (insert your "horse before the cart" jokes here).

Thanks for all the hard work Simba! This is the foundation for expansion work down the road.

Cheers,
Eric
Eric White
 
Posts: 54
Joined: Fri May 18, 2007 10:09 am

Cost and Portfolio dataset addition requests

Postby Eric White » Wed May 30, 2007 1:46 am

Do fellow Diehards feel it would be useful to add costs for each investment choice (absolute return - cost - inflation = real investor return) and include them in calculations? Sorry if it's already in there...

In addition, does anyone have datasets we can add for the following asset subclasses:

High Priority:
- Stable value/fixed (e.g. GICs, etc. offered via insurance to 401ks/403bs)
- Currencies? (to separate currency effect from International, European, Pacific, and EM returns; similar to inflation for separating investor returns)

Medium Prioriy:
- Int SV (no current Vanguard availability; ETF availability)
- EM SV (no current Vanguard availability; future ETF availability)
- Int REIT (no current Vanguard availability; mutual fund & ETF availability; new asset subclass may not have sufficient data)

Low Prioriy:
- Private Equity (no current Vanguard availability; future ETF availability?)
- International Bonds (no current Vanguard availability; mutual funds available)
- EM Bonds (no current Vanguard availability; mutual funds available)

Cheers,
Eric
Eric White
 
Posts: 54
Joined: Fri May 18, 2007 10:09 am

Rebalancing

Postby BornInBoston » Wed May 30, 2007 8:25 am

Amazing job, and superb collaberative work on this spreadsheet.

Question: is there any rebalancing involved in these calculations? Can't seem to find a reference to rebalancing. Sorry if I missed it. Lee
BornInBoston
 
Posts: 2
Joined: Wed May 30, 2007 8:23 am

Re: Cost and Portfolio dataset addition requests

Postby Cb » Wed May 30, 2007 8:29 am

Eric White wrote:Do fellow Diehards feel it would be useful to add costs for each investment choice (absolute return - cost - inflation = real investor return) and include them in calculations? Sorry if it's already in there...
Eric


I agree with you wrt to expene ratios, Eric. At present, in many (most?) of the asset classes, there is a bit of both. The 1972 - 199x returns are most often from MSCII-Barra or CRSP or another source that does NOT include typical fund ER's, and the returns used after the launh of the corresponding Vanguard fund in 199x - 2006 DO include the fund's ER.

I think it would be preferable to subtract the Vanguard funds's Investor Class ER from the earlier returns of the corresponding raw index also.

My preference would be to continue using nominal returns however, rather than subtracting one inflation index or another.

Cb
User avatar
Cb
 
Posts: 268
Joined: Wed Feb 21, 2007 12:39 am

Postby gummy » Wed May 30, 2007 9:55 am

Question: is there any rebalancing involved in these calculations?
Yup! Annual rebalancing to maintain the prescribed allocations.
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O

Lots of errors in the correlation

Postby edge » Thu May 31, 2007 9:50 am

Maybe I am missing something but it seems that nearly every calcuation of correlation is using a wrong column or all the columns in many of the correlation calcutaions are wrong in 5m.
edge
 
Posts: 1881
Joined: Mon Feb 19, 2007 7:44 pm
Location: Great Falls VA

Postby gummy » Thu May 31, 2007 11:53 am

The latest-and-greatest is available here:
http://www.gummy-stuff.org/Simba.htm

Simba has made a number of changes.
Check it out.
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O

Am I crazy?

Postby edge » Thu May 31, 2007 5:14 pm

I think the correlation numbers are wrong in this one too :p (?)
edge
 
Posts: 1881
Joined: Mon Feb 19, 2007 7:44 pm
Location: Great Falls VA

Re: Am I crazy?

Postby simba » Thu May 31, 2007 5:54 pm

edge wrote:I think the correlation numbers are wrong in this one too :p (?)


edge - I sent a PM to gummy this morning to update the SS on his website.
The latest version rev5p should be okay.

Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 7:47 pm

Postby johnb » Thu May 31, 2007 10:51 pm

Any chance of an unprotected version? I'm not able to change the blue cells on the newest version of the file. :( I'm on a Mac.
johnb
 
Posts: 282
Joined: Sat Mar 17, 2007 12:11 pm

Postby gummy » Fri Jun 01, 2007 2:47 am

Simba's rev5p is now here:
http://www.gummy-stuff.org/Excel/Simba-Backtest.xls

The "portfolio" sheet is unprotected. :D
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O

Postby johnb » Fri Jun 01, 2007 8:34 am

Thanks Gummy!
johnb
 
Posts: 282
Joined: Sat Mar 17, 2007 12:11 pm

Data source for EM?

Postby edge » Fri Jun 01, 2007 1:14 pm

I think the data source for EM has a developed market index listed - this does not seem correct.
edge
 
Posts: 1881
Joined: Mon Feb 19, 2007 7:44 pm
Location: Great Falls VA

Re: Data source for EM?

Postby simba » Fri Jun 01, 2007 2:05 pm

edge wrote:I think the data source for EM has a developed market index listed - this does not seem correct.


edge - thanks for pointing it out but that's just a typo error.

The EM returns for the duration 1972-87 were compiled by Robert T and I listed the source of the returns (These are not true EM returns either, they are a combination of 50% Intl Value & 50% Intl Small combination, check the IFA website for more info).

MSCI EM returns are available from 1987 and VG EM returns are available from 1995.

Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 7:47 pm

Yup

Postby edge » Sat Jun 02, 2007 12:24 pm

Simba,

Ya, I checked it myself and the actual data seemed fine :)
edge
 
Posts: 1881
Joined: Mon Feb 19, 2007 7:44 pm
Location: Great Falls VA

Un-rebalanced returns

Postby gbs » Sat Jun 02, 2007 12:58 pm

Simba,

I think it will be interesting to add the results for the un-rebalanced portfolio as well.

I did some testing on my own and in most cases for an all stock portfolio the returns as well as stdev go up.

gbs
User avatar
gbs
Wiki Admin
 
Posts: 558
Joined: Tue Feb 20, 2007 12:41 pm

Postby fundtalker123 » Sat Jun 02, 2007 6:41 pm

Ok, math-stat wizzes, I've got a question:

Suppose you've got this marvelous spreadsheet going and you're compulsively diddling around your asset allocation (heaven forbid, maybe you're even started messin' with SCV, EM, REITS, and Commodities). After diddling for 5 hours you get your Std. Dev. to PLUMMET from 13.8% to 12.8% and, at the same time, your CAGR SKYROCKETS from 10.8% to 11%!

How do you judge whether this change is statistically significant? It seems like some statistical test is in order. If there's a 55%/45% chance that my new portfolio is better/worse than my old one, I probably wouldn't bother making a change. But if it's 80%/20%, that might get me thinkin'. Any way to estimate this?
User avatar
fundtalker123
 
Posts: 868
Joined: Tue Feb 27, 2007 4:18 am

Error in SS?

Postby BornInBoston » Sun Jun 03, 2007 5:55 pm

When I try to put 100% in TSM (first item in asset list) I get error messages. Why won't the SS let me do this? Other combinations work fine, by the way.
BornInBoston
 
Posts: 2
Joined: Wed May 30, 2007 8:23 am

Re: Un-rebalanced returns

Postby simba » Wed Jun 06, 2007 1:26 pm

gbs wrote:Simba,

I think it will be interesting to add the results for the un-rebalanced portfolio as well.

I did some testing on my own and in most cases for an all stock portfolio the returns as well as stdev go up.

gbs


gbs - Thanks for the suggestion

The latest version rev6a can be downloaded here.

Some additions include (Thanks Gummy. Your help is very much appreciated).
- One can now compare 5 different portfolios
- Chart shows only the selected time periods
- SS shows returns for both rebalanced & un-rebalanced portfolios.

Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 7:47 pm

Postby gbs » Wed Jun 06, 2007 1:50 pm

Thanks Simba!

gbs
User avatar
gbs
Wiki Admin
 
Posts: 558
Joined: Tue Feb 20, 2007 12:41 pm

Postby fundtalker123 » Wed Jun 06, 2007 2:54 pm

Countour plots of sharpe ratio and compound annual return calculated from the 35 yr data, comparing various mixes of bonds and stocks, with various international, REIT, small cap value tilts, or with slice-dice are posted here:

http://www.gummy-stuff.org/FundTalker.htm

(the plots were done in Matlab and I don't know how to incorporate that in the Excel spreadsheet)
User avatar
fundtalker123
 
Posts: 868
Joined: Tue Feb 27, 2007 4:18 am

Some bugs

Postby edge » Wed Jun 06, 2007 5:39 pm

Hi,

I think some of the "real" returns are wrong, looks like one of the columns used in the equation are wrong.

It would be better if the inflation column was $'ed so the equation could just be copied/pasted.

A minor issue, one of the graph legend has "rebalanced" portfolio when it should be "unrebalanced"

Edited:

P2 P3 P4 P5 in the portfolio comparision section

Real returns are the same as nominal returns.
Last edited by edge on Thu Jun 07, 2007 9:59 am, edited 1 time in total.
edge
 
Posts: 1881
Joined: Mon Feb 19, 2007 7:44 pm
Location: Great Falls VA

Postby sterjs » Thu Jun 07, 2007 8:06 am

Interesting Result: Commodities don't improve Sharpe Ratios much for diversified portfolios, but they improve Sortino Ratios substantially.

I also messed around with the allocations and produced a portfolio that never had a losing year:

65% ST Treasuries
15% Commodities
10% U.S. Small Value
05% EAFE Value
05% Emerging Markets

CAGR 10.01%
Std. Dev. 4.62%
Sharpe 0.87
Sortino 2.97

P.S. The Intl Value looks strange to me... was there really only a .27% Value premium in EAFE from 72-2006?
Last edited by sterjs on Thu Jun 07, 2007 11:01 am, edited 1 time in total.
sterjs
 
Posts: 281
Joined: Sun Mar 25, 2007 2:22 am

Re: Some bugs

Postby simba » Thu Jun 07, 2007 10:43 am

edge wrote:Hi,

I think some of the "real" returns are wrong, looks like one of the columns used in the equation are wrong.

It would be better if the inflation column was $'ed so the equation could just be copied/pasted.

A minor issue, one of the graph legend has "rebalanced" portfolio when it should be "unrebalanced"

Edited:

P2 P3 P4 P5 in the portfolio comparision section

Real returns are the same as nominal returns.


Edge - Thanks for the correction. The latest version rev6b can be downloaded here
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 7:47 pm

Postby simba » Thu Jun 07, 2007 11:23 am

sterjs wrote:Interesting Result: Commodities don't improve Sharpe Ratios much for diversified portfolios, but they improve Sortino Ratios substantially.

I also messed around with the allocations and produced a portfolio that never had a losing year:

65% ST Treasuries
15% Commodities
10% U.S. Small Value
05% EAFE Value
05% Emerging Markets

CAGR 10.01%
Std. Dev. 4.62%
Sharpe 0.87
Sortino 2.97

P.S. The Intl Value looks strange to me... was there really only a .27% Value premium in EAFE from 72-2006?

sterjis,

I went back and verified the data for Intl Value and looked up VG's SEC filing. The data matched but Vanguard/Trustees' Equity Fund-International Portfolio became VG Intl Value (VTRIX) in 1997 so I went back and used the MSCI EAFE Value from 1975-1996 and VTRIX from 1997-2006 (instead of 1984-2006 that I was using earlier) now the value premium shows upto about 2% which is about the same for US Mkt as well.

Updated revision rev6c can be downloaded here

Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 7:47 pm

Postby jms969 » Thu Jun 07, 2007 4:12 pm

Here is a nice link to some excellent excel add-ins.

I am trying to apply the linear programming example to your spreadsheet to be able to change constraints and have it find an optimal solution. Solver only goes so far :)

http://www.me.utexas.edu/~jensen/ORMM/frontpage/jensen.lib/index.html

PS. Gummy you should like these :D
I want to die peacefully in my sleep like my grandfather, not screaming like the passengers in his car.
jms969
 
Posts: 156
Joined: Tue Feb 20, 2007 5:56 pm
Location: South Central Wisconsin

Postby gummy » Fri Jun 08, 2007 4:30 am

Alas, I can never seem to run Simba's spreadsheets after downloading. :(

Hmm ... gotta take a look at them add-ins. :D
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O

Postby sterjs » Sat Jun 09, 2007 8:29 pm

1985*-2006 Mid Cap Value, Growth

MC Value
0.32
0.18
-0.02
0.25
0.23
-0.16
0.38
0.22
0.16
-0.02
0.35
0.2
0.34
0.05
0.02
0.19
0.02
-0.1
0.38
0.24
0.13
0.2

MC Growth
0.32
0.18
0.03
0.13
0.32
-0.05
0.47
0.09
0.11
-0.02
0.34
0.18
0.23
0.18
0.51
-0.12
-0.2
-0.27
0.43
0.16
0.12
0.11

* MC Blend returns were used for 1985.

Source 1

Source 2
sterjs
 
Posts: 281
Joined: Sun Mar 25, 2007 2:22 am

SS updated with Midcap growth & Midcap value

Postby simba » Sun Jun 10, 2007 9:13 am

Latest version rev6d can be downloaded here.

This includes Mid-Cap growth & Mid-Cap Value.
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 7:47 pm

Postby Peppe » Sun Jun 10, 2007 9:26 am

Can you add VG extended market or other S&P completion type index? My 401k doesn't have total stock market, but has S&P 500 and the Russell small cap completion index. I used mid cap blend in place of it, but the completion index is more volatile than the VG mid cap.

Thanks for all your hard work this spreadsheet is fun to tinker with and is very helpful.
Peppe
 
Posts: 117
Joined: Mon Apr 16, 2007 1:26 pm

Postby simba » Sun Jun 10, 2007 3:04 pm

Peppe wrote:Can you add VG extended market or other S&P completion type index? My 401k doesn't have total stock market, but has S&P 500 and the Russell small cap completion index. I used mid cap blend in place of it, but the completion index is more volatile than the VG mid cap.

Thanks for all your hard work this spreadsheet is fun to tinker with and is very helpful.


Peppe,

I added VG extended Mkt returns to the spreadsheet. Latest version rev6e can be downloaded here

Regards,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 7:47 pm

Postby Zapped » Thu Jun 14, 2007 11:20 am

Has anyone sucessfully loaded the spreadsheet in OpenOffice? I'm using release 2.0.2 and even after enabling macros, it's not working correctly. I can't edit the blue boxes (OO says their protected cells), and the two graphs on the Portfolio sheet ("Portfolio Growth (Nominal) 19XX-2006") have nothing drawn in them.

I have access to Excel but I'm not a registered owner myself so I prefer to use OpenOffice. If anyone has experience getting Excel spreadsheets to work in OO I'd appreciate a conversion of Simba's latest rev6e.
- Jim in Austin, TX
User avatar
Zapped
 
Posts: 164
Joined: Wed May 30, 2007 3:00 pm
Location: Austin, TX

Postby kaesler » Thu Jun 14, 2007 11:39 am

Regarding OpenOffice I think I found that if I used Excel to remove the cell locking I could then use it in OpenOffice. Give that a try?
kaesler
 
Posts: 38
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Unprotect Cells

Postby CyberBob » Thu Jun 14, 2007 11:42 am

Zapped wrote:I can't edit the blue boxes (OO says their protected cells)

Just select Tools --> Protect Document and uncheck Sheet...

Bob
User avatar
CyberBob
 
Posts: 2946
Joined: Tue Feb 20, 2007 2:53 pm

Postby LH » Thu Jun 14, 2007 1:06 pm

I did as bob said, and OO.org spreadsheet allowed me to enter values, and the spreadsheet generated a graph and everything.

Often times, as with gummys spreadsheets, openoffice does not work with excel macros.

Also, I am using the Novell form of openoffice, which has extra excel macro support versus the regular openoffice. Often times, even with Novell openoffice, gummys spreadsheets do not work : ( But they are specifically working towards increasing the macro capability as I understand it.

If gummy and simba excel macro type spreadsheets are what you are trying to run using openoffice.org, I recommend downloading the novell form of openoffice.org from below. You will get less macro errors.

http://www.novell.com/products/desktop/ ... s/ooo.html

http://www.novell.com/news/press/novell ... perability
Last edited by LH on Thu Jun 14, 2007 11:47 pm, edited 2 times in total.
User avatar
LH
 
Posts: 5488
Joined: Wed Mar 14, 2007 2:54 am

PreviousNext

Return to Investing - Theory, News & General

Who is online

Users browsing this forum: jackholloway and 29 guests