My Personal Index - Now a Standalone Application

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

My Personal Index - Now a Standalone Application

Postby mattman22 » Mon May 19, 2008 9:48 pm

Welcome to My Personal Index. This free software allows you to create personal indexes which can be used to track performance, perform analysis, and compare different investing strategies. My Personal Index and its source code are released under the GNU General Public License v3.

Please try the software and leave your feedback, I am always looking for ways to improve it. There are also tutorials on my website's wiki.

You can download it here:
Windows: 3.2.0
MacOS (64-bit only): 3.2.0
Linux (portable version): 3.2.0

Go to http://code.google.com/p/mypersonalindex/ for more information and the source code.

Image

--------------------------------------------------------------------------------------------------------------------------------------------------------

This refers to an old Excel workbook. I now have the program mentioned above that I am actively working on. However, this spreadsheet still functions correctly.


This Excel workbook will help you effectively track the performance of your investment portfolio. The workbook has been designed to handle all of the calculations for you and gather the data; all you need to do is provide the tickers and shares. The spreadsheet makes extensive use of behind the scenes VBA code to effectively compute a pseudo mutual fund based on personal data (using a NAV).

I began working on this idea because it became difficult to reasonably track investments with dividends, purchases, sales, splits, etc. I assume other portfolio aids like this exist, but I made this in Excel both for the level of personal control (you can do your own analysis) and the simplicity.

I welcome all comments and criticisms. This is not meant to be a end-all solution, but I feel it provides enough computation to use the data in meaningful ways.

The Google Groups page is located at http://groups.google.com/group/mypersonalindex and the file can be downloaded from:

Excel 2003 Version (Deleted)
Excel 2007 Version (Save Link As...)

Download Sample Workbook (Deleted)

Make sure to enable macros! The workbook downloads data from Yahoo! Finance. The outputs page does not show anything until after the first update of prices. I recommend you read the instructions page carefully before starting. Also, look over the sample workbook for an introduction and general overview.

I hope this helps other people,

Matt

P.S.: One thing I did not make clear is that you should not have the enter a purchase for the tracking index (the gray column on the Shares page). This should only be used if the tracking index splits, otherwise it defaults to one share.

Update 1: Fixed error displaying on invalid ticker; Added sheet to list update and modifications; Set calculation of Excel to automatic on exit.

Update 2: Added the ability for asset allocation. Tweaked code and moved some things around.

Update 3: Added the ability for a desired asset allocation so you can keep the course.

If you want to transfer over to the newest version, set the "Data start date" to the same as your old version, copy over your tickers on the Inputs sheets, and then copy over the shares on the Shares sheet. Just click Update Prices and you have an exact copy on the new version.
Last edited by mattman22 on Sun Mar 11, 2012 12:54 am, edited 38 times in total.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Re: My Personal Index (Excel Spreadsheet)

Postby Ducks » Mon May 19, 2008 11:48 pm

Nice work Matt! Very impressive. :)

Does anybody else besides me wish there was a section in the Library (or something) for user-created Excel spreadsheets?
Getting our Ducks in a row since 2008.
User avatar
Ducks
 
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

Re: My Personal Index (Excel Spreadsheet)

Postby kukkomar » Mon May 19, 2008 11:59 pm

Thank you so much Matt !
I definitely agree with Ducks
kukkomar
 
Posts: 19
Joined: Fri Sep 14, 2007 10:37 am
Location: Finland

Re: My Personal Index (Excel Spreadsheet)

Postby celia » Tue May 20, 2008 12:15 am

Ducks wrote:Nice work Matt! Very impressive. :)

Does anybody else besides me wish there was a section in the Library (or something) for user-created Excel spreadsheets?

me!

(Although I know there are security risks when downloading things that contain macros)
User avatar
celia
 
Posts: 2075
Joined: Sun Mar 09, 2008 6:32 am
Location: SoCal

Postby MP173 » Tue May 20, 2008 10:42 am

Matt:

This appears to be a very useful tool. I have started several times to look at it, but the phone keeps ringing (gotta love those customers, particularly in a recesssionary era).

I have been looking for a method of tracking performance for "Ed's Mutual Fund", which is a group of 15 stocks that I have dividend re-investment program. With these equities, I have made discretionary purchases, based on availabiliity of $$$ and the attractiveness of the stocks. Tracking performance is not easy.

So, I take it that I can enter these symbols and then the quantity of shares for each. How do I handle the additional purchases? Are there any other features that you care to share about this? Does it provide the IRR for the grouping of stocks?

Thanks,

ed
MP173
 
Posts: 1454
Joined: Fri Dec 07, 2007 6:03 pm

Postby mattman22 » Tue May 20, 2008 11:00 am

MP173 wrote:So, I take it that I can enter these symbols and then the quantity of shares for each. How do I handle the additional purchases? Are there any other features that you care to share about this? Does it provide the IRR for the grouping of stocks?


Ed,

Sales and purchases can be entered easily. Just go down the column of the symbol on the "Shares" page and enter a new transaction on the next line. It may look something like this:

Code: Select all
            IBM
1/2/2008   100   $116.43
1/18/2008 (50)  $122.50


This shows that I bought 100 shares and soon after sold 50.

The spreadsheet does not provide an IRR, but it is very similar since it does provide the the total rate of return. If you view the "Methodology" page, it explains how the calculations are done. Look for the sample workbook I added in the original post.

Matt
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby MP173 » Tue May 20, 2008 3:42 pm

Matt:

Thanks, I hope to have a chance to take a look at this in detail tonight.

I am always amazed at the Excel architects out there that put these things together.

Surely there will be more questions....

ed
MP173
 
Posts: 1454
Joined: Fri Dec 07, 2007 6:03 pm

Postby gkaplan » Tue May 20, 2008 3:56 pm

Any 2000 versions?
Gordon
gkaplan
 
Posts: 5413
Joined: Sat Mar 03, 2007 8:34 pm
Location: Portland, Oregon

Postby mattman22 » Tue May 20, 2008 4:28 pm

gkaplan wrote:Any 2000 versions?


I assume the Excel 2003 version will work in 2000. However, I am unable to test it and have no way of knowing if everything calculates correctly.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby tetractys » Tue May 20, 2008 4:48 pm

I have an interest in these types of things. But no way would I ever open a file like this, containing macros, because of the SECURITY RISK! First the file should be guaranteed and certified safe somehow. Also I feel it's disrespectful in general, regardless of any high intentions, for any poster to expect people to blindly trust the safety of downloads like this.

Possibly the forum moderators could be involved?

Best regards, Tet
User avatar
tetractys
 
Posts: 4204
Joined: Sat Mar 17, 2007 3:30 pm
Location: Along the Salish Sea

Postby mattman22 » Tue May 20, 2008 6:06 pm

I understand your concern, but you can open the file with macros disabled and view all the code. If you do not know VBA, then you can have someone else look at it who does.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby serbeer » Tue May 20, 2008 6:32 pm

tetractys wrote: First the file should be guaranteed and certified safe somehow.

Matt is generously sharing the tool he probably spent very significant time to craft with the rest of us. Tet's concern is valid given that Matt is relatively new member of the board, but in this case there is no issue.

For what it is worth, I disallowed macros to run on the first open and looked at VB code close enough to make sure it does no harm, before restarting Excel with macros enabled. So, for whatever my "certification" is worth" the version for Excel 2003 does only what is advertised--and after I play with it more, I'll know how well.

Thanks for sharing your work Matt!
User avatar
serbeer
 
Posts: 916
Joined: Fri Dec 28, 2007 2:09 pm

Postby tat2ng » Tue May 20, 2008 8:30 pm

mattman22 wrote:
gkaplan wrote:Any 2000 versions?


I assume the Excel 2003 version will work in 2000. However, I am unable to test it and have no way of knowing if everything calculates correctly.


I'm using my Mac, and don't have my PC turned on, but I seem to recall that Excel 2000 doesn't have the add-on to download the stock quotes from Yahoo or something like that.

Anyone else able to verify that?

Thad
User avatar
tat2ng
 
Posts: 463
Joined: Tue Feb 20, 2007 9:15 pm
Location: Michigan

Postby MP173 » Tue May 20, 2008 9:05 pm

I tried using it and didnt get past the first entry on the "Set Data Start Date". A window came up indicating it couldnt find the macros.

I was really hoping this would be easy to use. The sample looked like it had pretty good information.

ed
MP173
 
Posts: 1454
Joined: Fri Dec 07, 2007 6:03 pm

Postby mattman22 » Tue May 20, 2008 9:21 pm

tat2ng wrote:I'm using my Mac, and don't have my PC turned on, but I seem to recall that Excel 2000 doesn't have the add-on to download the stock quotes from Yahoo or something like that.


The spreadsheet downloads the quotes in csv format from Yahoo. It does not depend on the add-on.

MP173 wrote:I tried using it and didnt get past the first entry on the "Set Data Start Date". A window came up indicating it couldnt find the macros.


This is because macros were not enabled. Just make sure to set your security to a level that allows you to use the macros. See http://office.microsoft.com/en-us/ork2003/HA011403071033.aspx for more information. I hope it is easy to use, but I cannot bypass the "high" macro security (for good reason) which does not even prompt to use macros.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby schellhase » Wed May 21, 2008 12:13 am

Thank you Matt, I entered my data tonight and I'll see what it looks like tomorrow. I downloaded your spreadsheet yesterday. And today my version of Excel 2003 has a quirk. It will allow me to drag a formula for a column of numbers to the cells below it and it propagates the proper formula in the successive cells however the values in the new column are now all equal to the value of the first entry (the object of the drag) until I actually individually go to each cell, highlight the formula in the formula bar and hit enter.

In the example below I dragged cell C1 to C2 through C4 and each cell in col C has the correct formula =SUM(A1*B1) through =SUM(A4*B4). But the proper values will not display unless I actually go to C2 thru C4 click on the formula in the formula bar and hit enter.

Any ideas?

123 2 246
456 1 246
789 2 246
321 1 246

Thanks again,
Larry
User avatar
schellhase
 
Posts: 312
Joined: Sun Mar 04, 2007 7:07 pm
Location: Los Angeles, CA

Postby mattman22 » Wed May 21, 2008 12:41 am

schellhase wrote:Any ideas?


Larry, this is caused by what is known as manual calculation of formulas. I set the workbook to use manual calculation because every cell would recalculate after each change, rather than when needed, causing major slowdowns with lots of data. If my workbook was the last opened, it will keep manual calculation on a brand new workbook (you have to press F9 to update all cells). You want to change back to automatic calculation of formulas:

How to change the mode of calculation in Excel 2003 and in earlier versions of Excel
1. Click Options on the Tools menu, and then click the Calculation tab.
2. Under Calculation, click the calculation mode that you want to use.

How to change the mode of calculation in Excel 2007
1. Click the Microsoft Office Button, and then click Excel Options.
2. On the Formulas tab, select the calculation mode that you want to use.

Hope this helps, and sorry for the confusion.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby MP173 » Wed May 21, 2008 6:07 am

Ok Matt, I am going to leave my comfort zone and get this going. You have to understand that I dont know too much about Excel.

I read the link you referenced regarding setting the macros. What level would you suggest that it be set at?

So, here are the steps:
1. Download and save the file.
2. Reset the settings for macros.
3. Enter data.

Is that it? Anything else, even remotely simple that I should do?

BTW, was this a college project? or just something you did on your own?

ed
MP173
 
Posts: 1454
Joined: Fri Dec 07, 2007 6:03 pm

Re: My Personal Index (Excel Spreadsheet)

Postby Tramper Al » Wed May 21, 2008 8:14 am

mattman22 wrote:This Excel workbook will help you effectively track the performance of your investment portfolio.

Matt,

This looks very interesting, thanks.

It seems to rely on tickers and downloaded prices. Is there a provision for investments that do not have tickers, such that I can update those prices manually?

Also, I see "do not edit" for the Index sheet. Can the user specify the index he wants, or better yet construct a weighted index of various components that add to 100%?

Finally, is there no way to assign an asset class to each investment, so that the overall %AA can be viewed and tracked? This is the major function of my current homemade crude spreadsheet. It also keeps my cost bases, and computes my after-tax value AA, but its only "performance" feature is one where I have simulated a weighted index benchmark by using a portfolio of index funds to give me quarterly, annual, YTD, etc. % returns.
Tramper Al
 
Posts: 3576
Joined: Thu Oct 18, 2007 11:42 am

Postby grayfox » Wed May 21, 2008 8:33 am

I tried running it on a Macintosh using Microsoft Excel 2004 for Macintosh.
When I hit Update Prices this error window pops up

Code: Select all
 Compile Error:

 Automation type not supported in Visual Basic


Then the debugger comes up and this line is highlighted:
Code: Select all
'Download all close prices and dividends for the index and symbols
Public Sub InsertCloses()


The same error come up if I hit Clear All but a different line:
Code: Select all
'Clear all data and set start date to current date
Sub ClearAll()
User avatar
grayfox
 
Posts: 4071
Joined: Sat Sep 15, 2007 4:30 am
Location: Anytown, USA

Help for Mac user

Postby dd434 » Wed May 21, 2008 8:54 am

Thanks for the spreadsheet.

I recently installed Office 2008 for mac.
When I try and open the spreadsheet it says I can not enable macros for this document.
Is there ant way I can still use this spreadsheet?

I'm not very computer literate.

thanks
dd434
 
Posts: 17
Joined: Wed Mar 19, 2008 11:03 am

Re: Help for Mac user

Postby stratton » Wed May 21, 2008 10:11 am

dd434 wrote:I recently installed Office 2008 for mac.
When I try and open the spreadsheet it says I can not enable macros for this document.
Is there ant way I can still use this spreadsheet?

Mac Office 2008 doesn't have Visual Basic for Applications (VBA) in it any more. Microsoft removed it. You have more of a chance of getting it to work in Mac Office 2004 if you have that. Microsoft said they are putting VBA back in the next version of Mac Office.

Paul
User avatar
stratton
 
Posts: 10814
Joined: Sun Mar 04, 2007 5:05 pm
Location: Puget Sound

Postby mattman22 » Wed May 21, 2008 10:57 am

dd434 wrote:I recently installed Office 2008 for mac.
When I try and open the spreadsheet it says I can not enable macros for this document.
Is there ant way I can still use this spreadsheet?


grayfox wrote:I tried running it on a Macintosh using Microsoft Excel 2004 for Macintosh.
When I hit Update Prices this error window pops up


I do not have a Mac, but like Paul said, Microsoft removed VBA from the Mac Office. I'm sorry it won't work, but I am looking into getting an OpenOffice version for Mac/Linux.

MP173 wrote:Is that it? Anything else, even remotely simple that I should do?

BTW, was this a college project? or just something you did on your own?


After enabling macros (I would use medium security, it will prompt you when you open the spreadsheet to enable or disable macros), set the data start date, enter the ticker symbols on the Inputs page, then the purchases and sales on the Shares page. Click Update Prices and you should be all set.

This was something I did on mine own (with the help of a roommate), but I did it during my last year at college.

Tramper Al wrote:It seems to rely on tickers and downloaded prices. Is there a provision for investments that do not have tickers, such that I can update those prices manually?


This is possible, but obviously must be done manually like you said. Enter a phrase where you would normally enter the ticker symbol on the Inputs sheet. Hit Apply Changes, and then head over to the Prices sheet. Find the column of the phrase you just typed and fill in the corresponding close prices in relation to the date on the far left. If you have dividends, enter those also. The shares will update automatically from what you enter on the Shares sheet.

Tramper Al wrote:Also, I see "do not edit" for the Index sheet. Can the user specify the index he wants, or better yet construct a weighted index of various components that add to 100%?


The Index sheet only does 2 things. It calculates the weighted return of all the tickers in your portfolio on the left. On the right it calculates the return of your tracking index, which can be changed from the Inputs sheet. The components of your portfolio will always add to 100%. If you want to change the make-up, simply uncheck the checkboxes on the Inputs sheet to change around the weighting. There is no way to do percentages rather than shares, since this workbook was mainly designed to track a person's actual portfolio.

Tramper Al wrote:Finally, is there no way to assign an asset class to each investment, so that the overall %AA can be viewed and tracked?


This is a very good idea and not something I thought to put in. I will get that feature going for the next version around.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby dothemontecarlo » Wed May 21, 2008 1:29 pm

mattman22 wrote:
schellhase wrote:Any ideas?


Larry, this is caused by what is known as manual calculation of formulas. I set the workbook to use manual calculation because every cell would recalculate after each change, rather than when needed, causing major slowdowns with lots of data. If my workbook was the last opened, it will keep manual calculation on a brand new workbook (you have to press F9 to update all cells). You want to change back to automatic calculation of formulas:

How to change the mode of calculation in Excel 2003 and in earlier versions of Excel
1. Click Options on the Tools menu, and then click the Calculation tab.
2. Under Calculation, click the calculation mode that you want to use.

How to change the mode of calculation in Excel 2007
1. Click the Microsoft Office Button, and then click Excel Options.
2. On the Formulas tab, select the calculation mode that you want to use.

Hope this helps, and sorry for the confusion.


Matt, may I make an editing suggestion for your spreadsheet:

In VBA, select "ThisWorkBook" and "BeforeClose" Event and add some code to turn automatic recalculations back on for users when they close your workbook. That way they won't think they caught a virus by running your program. Something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application
.ScreenUpdating = True
.StatusBar = False
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub


-dothemontecarlo
User avatar
dothemontecarlo
 
Posts: 532
Joined: Tue Nov 06, 2007 10:19 am
Location: Colorado

Postby mattman22 » Wed May 21, 2008 4:04 pm

dothemontecarlo, thanks for the suggestion. I will be sure to add it.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby Murple » Thu May 22, 2008 12:12 pm

Matt, this spreadsheet is great. I've been fooling around with it for the past couple of days and it seems to be working fine. Anyone out there surprised by their results?

Also, how is that OpenOffice version coming? I prefer to use open source programs whenever possible.

Thanks again, this is great.
Murple
 
Posts: 3
Joined: Thu May 01, 2008 12:48 pm

Postby grayfox » Fri May 23, 2008 1:22 am

grayfox wrote:
I tried running it on a Macintosh using Microsoft Excel 2004 for Macintosh.
When I hit Update Prices this error window pops up


I do not have a Mac, but like Paul said, Microsoft removed VBA from the Mac Office. I'm sorry it won't work, but I am looking into getting an OpenOffice version for Mac/Linux.


I have Excel 2004 and it does have Visual Basic for Applications. So the problem is not that I don't have VBA. For some reason there is a problem porting from whatever platform you developed it on to a Macintosh running Excel 2004.
User avatar
grayfox
 
Posts: 4071
Joined: Sat Sep 15, 2007 4:30 am
Location: Anytown, USA

Postby Deacon Mike » Fri May 23, 2008 11:20 am

First, this is an awesome spreadsheet. It tells me that since I've started passive investing in October 2006, I'm out performing the S&P. I have a couple of questions.

I'm own all mutual funds, with dividend and gains automatically reinvested. I've put those reinvestment as share purchases on the share page. Would I then use "Include Dividends" or not.

Also the yearly return number on the output tab is not calculated correctly, I don't think.
Deacon Mike
 
Posts: 136
Joined: Fri Jun 01, 2007 12:54 pm

Postby mattman22 » Fri May 23, 2008 11:22 am

Murple wrote:Also, how is that OpenOffice version coming? I prefer to use open source programs whenever possible.


The OpenOffice version is coming along slowly. It seems to have VBA, but work differently than normal Excel.

grayfox wrote:I have Excel 2004 and it does have Visual Basic for Applications. So the problem is not that I don't have VBA. For some reason there is a problem porting from whatever platform you developed it on to a Macintosh running Excel 2004.


The spreadsheet was created and saved in Excel 2003 and Excel 2007 on Windows. I really wish I knew more about the differences on a Mac, but the error, "Automation type not supported in Visual Basic," does not help me pinpoint what the problem is.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby serbeer » Fri May 23, 2008 11:53 am

Matt,
I am yet to get to testing the spreadsheet, no free time at all lately but have suggestion in the meantime--since I am sure you'll be making revisions from time to time, it would be a good idea to keep revisions history in a separate or one of the existing tabs of the spreadsheet (just current version number, release date, and very brief description of the changes). That would allow to notice any updates and understand what changed. That's the approach taken by pretty much every other Excel tools author out there (e.g. analyzenow.com).

I noticed that there was some change during the day on May 20--the files I downloaded right after your original post and the files available now are of different size (became smaller actually)--but have no idea what changed, probably clean up, and most of the people would not notice the update to begin with.

Thanks!
User avatar
serbeer
 
Posts: 916
Joined: Fri Dec 28, 2007 2:09 pm

Postby mattman22 » Sat May 24, 2008 8:00 pm

serbeer wrote:I am yet to get to testing the spreadsheet, no free time at all lately but have suggestion in the meantime--since I am sure you'll be making revisions from time to time, it would be a good idea to keep revisions history in a separate or one of the existing tabs of the spreadsheet (just current version number, release date, and very brief description of the changes). That would allow to notice any updates and understand what changed. That's the approach taken by pretty much every other Excel tools author out there (e.g. analyzenow.com).


Done and updated! I also set the Excel calculation back to automatic on close. The next thing on my list is to create a way to show the asset allocation of the portfolio.

I haven't heard much about success or failure stories with the personal index. Anybody wish to share their experience?
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby MP173 » Mon May 26, 2008 1:43 pm

Mattman:

I tried a couple of times to get this running, but couldnt. It sure looks great, something I have been trying to figure out for a couple of years.

Personally, I have a group of stocks (15) that I have had for a number of years thru DRP programs. I have done well, but am not sure how well I have done. This spreadsheet is just the tool needed to monitor performance.

ed
MP173
 
Posts: 1454
Joined: Fri Dec 07, 2007 6:03 pm

Postby detifoss » Tue May 27, 2008 1:03 am

grayfox wrote:I tried running it on a Macintosh using Microsoft Excel 2004 for Macintosh.
When I hit Update Prices this error window pops up

Code: Select all
 Compile Error:

 Automation type not supported in Visual Basic


Then the debugger comes up and this line is highlighted:
Code: Select all
'Download all close prices and dividends for the index and symbols
Public Sub InsertCloses()


The same error come up if I hit Clear All but a different line:
Code: Select all
'Clear all data and set start date to current date
Sub ClearAll()


I too have the same problem, on a Mac, and it ocurred right from the outset when I tried to change the Start Date...

It looks like an amazing application, just what I have been looking for, but my only access to a PC is at work, and I think I won't have the security ability to enable macros there. I wonder if I could try running Windows from my Mac and then use Excel? I've never done it, nor do I have Windows or Office for windows, so I wonder if I have a way around any of this... Any Mac users find success using this spreadsheet?
detifoss
 
Posts: 554
Joined: Sat Oct 13, 2007 10:38 pm

Postby grayfox » Tue May 27, 2008 1:55 am

detifox wrote:I too have the same problem, on a Mac, and it ocurred right from the outset when I tried to change the Start Date...

It looks like an amazing application, just what I have been looking for, but my only access to a PC is at work, and I think I won't have the security ability to enable macros there. I wonder if I could try running Windows from my Mac and then use Excel? I've never done it, nor do I have Windows or Office for windows, so I wonder if I have a way around any of this... Any Mac users find success using this spreadsheet?


I'm sure that it is possible to port this spreadsheet to a Mac running Office 2004. There is probably some minor change that needs to be made. (I am not the person to do it because I don't know Visual Basic and don't have time to learn it.) I have downloaded many spreadsheets from gummy's website, simba's backtest spreadsheet plus many others and they all run fine. My guess is there is one little thing in this spreasheet that makes the code not portable.

It is a shame that Microsoft does not make the spreadsheets 100% inter-operable between the various platforms. One little difference and your program won't run.
User avatar
grayfox
 
Posts: 4071
Joined: Sat Sep 15, 2007 4:30 am
Location: Anytown, USA

Postby mattman22 » Tue May 27, 2008 7:59 am

MP173 wrote:I tried a couple of times to get this running, but couldnt. It sure looks great, something I have been trying to figure out for a couple of years.


What went wrong? Maybe I can help...

detifoss wrote:I too have the same problem, on a Mac, and it ocurred right from the outset when I tried to change the Start Date...


I believe I may know what the problem is, and it might have to do with getting the data from Yahoo! Finance. Unlike gummy's spreadsheets, I download the data using what is known as a Windows API call, and I can only assume these aren't ported into the Mac version. I will figure out another way to get the data and we'll see if that fixes the problem.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby gvernon » Tue May 27, 2008 9:24 am

matt-

first of all, thanks for the spreadsheet. i think it will be a great tool.

however, i'm having two main problems.

1) the sheet works the first time i open it up and set it up, but then if i save it and re-open it, none of the calculations on the output sheet except for "days invested" work. everything else returns, "VALUE!". same with the "idex" tab - the total value of my portfolio calculates correctly, but all other columns display "VALUE!"

2) the graph on the output page has a 1st day offset between my index and VFINX. my index starts at 0%, but VFINX plots it's first point at a little less than -1%. i think this is also true of your example sheet which i believe contains the same problem. it's as if VFINX starts plotting one day earlier than my index (and apparently lost ~1% of value on that day).

i know i explained both of those things very sloppily, so if you need clarification feel free to PM me.

-gvernon

EDIT: I downloaded the new version and #1 seems to be fixed (I'll be able to tell for sure tomorrow when I "update prices"). #2 was my falut because I had listed the closing price as price paid in the "shares" tab rather than the opening price, thereby offsetting the two series by one day.
gvernon
 
Posts: 124
Joined: Fri Apr 20, 2007 7:28 pm
Location: Houston, TX

Postby gvernon » Tue May 27, 2008 10:14 am

sorry, one other problem i forgot to mention...

i haven't tried yet on the updated version, but when i had tried to include VMMXX as part of "my index" the sheet would calculate it as part of my total portfolio value, but then it would display as 0% of my total portfolio. weird.

it probably has something to do with VMMXX's closing price never changing from 1.00??

thanks,
gvernon
gvernon
 
Posts: 124
Joined: Fri Apr 20, 2007 7:28 pm
Location: Houston, TX

Postby mattman22 » Tue May 27, 2008 6:37 pm

gvernon wrote:i haven't tried yet on the updated version, but when i had tried to include VMMXX as part of "my index" the sheet would calculate it as part of my total portfolio value, but then it would display as 0% of my total portfolio. weird.

it probably has something to do with VMMXX's closing price never changing from 1.00??


gvernon,

Glad the first 2 problems worked themselves out. As for the VMMXX problem, a share value of $1 should not be a problem. However, from looking on Yahoo Finance, I see the problem right away. The spreadsheet downloads historical data to fill in the close prices. Unfortunately, Yahoo does not offer historical prices for VMMXX, probably because the price is always 1.00. Because of this, the only way to include VMMXX is to manually drag the price of 1.00 down the VMMXX Close column on the "Prices" sheet.

You can drag it down as far as you want, and it won't effect the calculation. I recommend dragging it down a few hundred rows so you don't have to constantly update it. Just make sure when you get dividends from VMMXX, you include the number of shares on the "Shares" sheet at purchase price of $0.00, so you can realize the gain.

Hope this helps. Thanks for testing it out, it helps make it more useful for everyone.

Matt
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby gvernon » Wed May 28, 2008 9:52 am

thanks matt,

the work around for VMMXX worked like a charm!

thanks again for all your hard work, i'll keep you posted if i find any other issues.

gvernon
gvernon
 
Posts: 124
Joined: Fri Apr 20, 2007 7:28 pm
Location: Houston, TX

Postby mattman22 » Thu May 29, 2008 1:00 am

Just updated the spreadsheet. Main difference is you can now do a simple asset allocation of your portfolio. Also some other minor tweaks. Still haven't figured out the Mac version, but I haven't forgotten about it.

To update, follow these instructions:

If you want to transfer over to the newest version, set the "Data start date" to the same as your old version, copy over your tickers on the Inputs sheets, and then copy over the shares on the Shares sheet. Just click Update Prices and you have an exact copy on the new version.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby dmw » Thu May 29, 2008 9:04 pm

Matt,

I tried to download the 2007 version and I just got another tab open in my browser that was full of like wingding looking text. Is the link broken? Thanks!


EDIT:
Nevermind I did the right click save link as and it downloaded fine.

When I opened it though I clicked the 'clear all' button on the instructions page and I got an error of:

Compile error:
Can't find project or library

Thanks for any help!
dmw
 
Posts: 20
Joined: Fri Sep 07, 2007 10:12 am
Location: minnesota

Postby mattman22 » Thu May 29, 2008 10:41 pm

dmw wrote:When I opened it though I clicked the 'clear all' button on the instructions page and I got an error of:

Compile error:
Can't find project or library


dmw,

It sounds like you are running Excel 2003. If you have Excel 2003, only the Excel 2003 version will work. If this is Excel 2007, then something weird is going on. I will have to look into it further if that is the case.

Let me know,
Matt
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby dmw » Fri May 30, 2008 7:20 am

dmw,

It sounds like you are running Excel 2003. If you have Excel 2003, only the Excel 2003 version will work. If this is Excel 2007, then something weird is going on. I will have to look into it further if that is the case.


Matt,

I have Excel 2007. I just bought a new computer last month and it came with Office Basic 2007.

Since I had problems with 2007 I tried using the Excel 2003 version and that one worked with the clear all button. I did encounter the same problem as Gvernon though when I updated prices the output tab and index tab just returned all #VALUE! errors.
dmw
 
Posts: 20
Joined: Fri Sep 07, 2007 10:12 am
Location: minnesota

Postby mattman22 » Fri Jun 13, 2008 4:02 pm

dmw wrote:I have Excel 2007. I just bought a new computer last month and it came with Office Basic 2007.


dmw, try and see if it works now. I removed some references that Excel adds that may not be included in Office Basic 2007.

The spreadsheet is now updated to allow for a desired asset allocation and it tracks the margin of error given your current portfolio. To update to the new version, follow the instructions at the bottom of the original first post.
User avatar
mattman22
 
Posts: 192
Joined: Sun Dec 30, 2007 11:51 am
Location: Boston, MA

Postby gvernon » Fri Jun 13, 2008 4:34 pm

Thanks Matt!

Great work!
gvernon
 
Posts: 124
Joined: Fri Apr 20, 2007 7:28 pm
Location: Houston, TX

Postby detifoss » Sat Jun 14, 2008 12:46 pm

thanks matt
i was able to get this to work at my office, since they have a PC, and it is fantastic.

I'm hoping you will be able to solve the problem with the Mac version one day so all of the mac users without PC access (like me) will be able to thank you for your great work too!
detifoss
 
Posts: 554
Joined: Sat Oct 13, 2007 10:38 pm

Quarterly output?

Postby TallyMan » Sun Jun 15, 2008 2:53 am

Matt, Thx for sharing your work with us, both the professional looking product as well as the commented code.

I have my wife and my finances set up so that I do a report quarterly and anticipate distributing retirement income from our investments quarterly. Any chance of a future version having an option of reporting "outputs" quarterly?

In any event, thx again for sharing your hard work!
Steve
TallyMan
 
Posts: 135
Joined: Wed Dec 26, 2007 10:04 pm

Postby Ducks » Tue Dec 09, 2008 11:45 am

Bumping this up to ask mattman if he's still around and updating this spreadsheet. I'd forgotten about it until I went digging in my "Ducks" email folder and found the thread. :)
Getting our Ducks in a row since 2008.
User avatar
Ducks
 
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

Postby detifoss » Tue Dec 09, 2008 2:38 pm

and wondering if it has been fixed for macs... :D

I use it on my work PC and it has been great - thanks!

the only bad news is that I think my use of it caused the markets to crash - I started tracking my portfolio 5/23/08 for the first time ever

oops!
detifoss
 
Posts: 554
Joined: Sat Oct 13, 2007 10:38 pm

Postby diasurfer » Tue Dec 09, 2008 2:51 pm

Strange, I was just thinking about this tool this morning and was wondering how I would find the thread. Thanks Ducks!
diasurfer
 
Posts: 1820
Joined: Fri Jul 06, 2007 8:33 pm
Location: miami-dade

Next

Return to Investing - Theory, News & General

Who is online

Users browsing this forum: AviN, Bracket, Google [Bot], happyisland, JamesSFO, jimcrawford01, Johno, Mel Lindauer, nvst, Rodc, rrkulkarni, sscritic, Taylor Larimore, TheTimeLord and 92 guests