My Personal Index - Now a Standalone Application

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

My Personal Index - Now a Standalone Application

Post by mattman22 »

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 Sat Mar 10, 2012 11:54 pm, edited 38 times in total.
User avatar
Ducks
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

Re: My Personal Index (Excel Spreadsheet)

Post by Ducks »

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.
kukkomar
Posts: 19
Joined: Fri Sep 14, 2007 10:37 am
Location: Finland

Re: My Personal Index (Excel Spreadsheet)

Post by kukkomar »

Thank you so much Matt !
I definitely agree with Ducks
User avatar
celia
Posts: 16774
Joined: Sun Mar 09, 2008 6:32 am
Location: SoCal

Re: My Personal Index (Excel Spreadsheet)

Post by celia »

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)
MP173
Posts: 2609
Joined: Fri Dec 07, 2007 5:03 pm

Post by MP173 »

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
User avatar
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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
MP173
Posts: 2609
Joined: Fri Dec 07, 2007 5:03 pm

Post by MP173 »

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
gkaplan
Posts: 7034
Joined: Sat Mar 03, 2007 7:34 pm
Location: Portland, Oregon

Post by gkaplan »

Any 2000 versions?
Gordon
User avatar
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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
tetractys
Posts: 6249
Joined: Sat Mar 17, 2007 3:30 pm
Location: Along the Salish Sea

Post by tetractys »

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
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

Post by serbeer »

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
tat2ng
Posts: 475
Joined: Tue Feb 20, 2007 8:15 pm
Location: Michigan
Contact:

Post by tat2ng »

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
MP173
Posts: 2609
Joined: Fri Dec 07, 2007 5:03 pm

Post by MP173 »

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
User avatar
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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/ork20 ... 71033.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
schellhase
Posts: 311
Joined: Sun Mar 04, 2007 6:07 pm
Location: Los Angeles, CA

Post by schellhase »

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
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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.
MP173
Posts: 2609
Joined: Fri Dec 07, 2007 5:03 pm

Post by MP173 »

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
Tramper Al
Posts: 3665
Joined: Thu Oct 18, 2007 11:42 am

Re: My Personal Index (Excel Spreadsheet)

Post by Tramper Al »

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.
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

Post by grayfox »

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()
dd434
Posts: 17
Joined: Wed Mar 19, 2008 11:03 am

Help for Mac user

Post by dd434 »

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
User avatar
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

Re: Help for Mac user

Post by stratton »

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
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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
dothemontecarlo
Posts: 539
Joined: Tue Nov 06, 2007 9:19 am
Location: Colorado
Contact:

Post by dothemontecarlo »

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
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

dothemontecarlo, thanks for the suggestion. I will be sure to add it.
Murple
Posts: 3
Joined: Thu May 01, 2008 12:48 pm

Post by Murple »

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.
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

Post by grayfox »

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.
Deacon Mike
Posts: 136
Joined: Fri Jun 01, 2007 12:54 pm

Post by Deacon Mike »

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.
User avatar
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

Post by serbeer »

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
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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?
MP173
Posts: 2609
Joined: Fri Dec 07, 2007 5:03 pm

Post by MP173 »

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
detifoss
Posts: 556
Joined: Sat Oct 13, 2007 10:38 pm

Post by detifoss »

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?
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

Post by grayfox »

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
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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.
gvernon
Posts: 124
Joined: Fri Apr 20, 2007 7:28 pm
Location: Houston, TX

Post by gvernon »

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

Post by gvernon »

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
User avatar
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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
gvernon
Posts: 124
Joined: Fri Apr 20, 2007 7:28 pm
Location: Houston, TX

Post by gvernon »

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
User avatar
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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.
dmw
Posts: 20
Joined: Fri Sep 07, 2007 10:12 am
Location: minnesota

Post by dmw »

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!
User avatar
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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
dmw
Posts: 20
Joined: Fri Sep 07, 2007 10:12 am
Location: minnesota

Post by dmw »

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.
User avatar
Topic Author
mattman22
Posts: 192
Joined: Sun Dec 30, 2007 10:51 am
Location: Boston, MA
Contact:

Post by mattman22 »

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.
gvernon
Posts: 124
Joined: Fri Apr 20, 2007 7:28 pm
Location: Houston, TX

Post by gvernon »

Thanks Matt!

Great work!
detifoss
Posts: 556
Joined: Sat Oct 13, 2007 10:38 pm

Post by detifoss »

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!
TallyMan
Posts: 137
Joined: Wed Dec 26, 2007 9:04 pm

Quarterly output?

Post by TallyMan »

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
User avatar
Ducks
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

Post by Ducks »

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.
detifoss
Posts: 556
Joined: Sat Oct 13, 2007 10:38 pm

Post by detifoss »

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!
diasurfer
Posts: 1855
Joined: Fri Jul 06, 2007 8:33 pm
Location: miami-dade

Post by diasurfer »

Strange, I was just thinking about this tool this morning and was wondering how I would find the thread. Thanks Ducks!
Post Reply