Old School Value Investment Portfolio Spreadsheet

February 12, 2008 | Comments (33)

Written by

Jae Jun

follow me on

Facebook

Twitter

[edit] Newer Version of Investment Portfolio Spreadsheet Available

There is now an even better version available. A brand new and updated version of the best stock portfolio tracking spreadsheet. Go check it out now.

Go see how the stock value calculator can help you value stocks quickly, accurately and save you time.

Continue on with the Original Article..

For an investor, keeping track of personal performance and gains is important. Many people believe they know their return rate in their head, but surprisingly, this is always overestimated. Our actual return usually come out a few % lower and these few percentages can add up to ten of thousands. That is why a investment portfolio spreadsheet is important to track performance.

Tracking Your Performance

Tracking performance also allows an investor to be honest with themselves. As human beings, we tend to dismiss the failures much more often than when we try to acknowledge and learn from them. Everyone has made investment mistakes and it is vital to know what went wrong and then to never redo those mistakes. I believe that monitoring your portfolio in a spreadsheet or other form reminds you of those mistakes.

The Original Excel Investing Portfolio Spreadsheet

I was sick of all the portfolio managing programs that you had to buy and install etc, so I made my own. But one day, I came across a great blog titled ‘Experiments in Finance’ where the author had created a great Portfolio Performance vs S&P 500 spreadsheet. Please visit the blog to get the original file and see the instructions.

Automated Excel Investment Portfolio Spreadsheet

Now if you’re like me and can’t be bothered looking up the latest prices and then recording them manually, a great plugin for excel, written by Randy, is available at Randy’s Yahoo Group. You can find more info by going to ‘Files > Documentation’ section on the Yahoo group. For your convenience, I’ve made a link so you can download it right here.

Utilizing the excel add-in, the latest and historical stock prices for all tickers can be retrieved. You just add the stock symbol, no. of shares bought, purchase price and date of purchase and the rest should be updated.

Spreadsheet Installation Instructions

The instructions are pretty simple, download the zip file containing the add-in and its supplementary files and create a folder called “SMF” directly under C: and then extract all of the files into the “C:\SMF\” folder on your computer.

Then open Excel, make sure a blank workbook is open, choose the menu Tools->Add-Ins, in the dialog box which opens choose Browse and choose the folder “C:\SMF\” and then choose the file “RCH_Stock_Market_Functions.xla” and click OK.

edit: follow the link to view the full and comprehensive installation manual and FAQ.

Few Quick Comments

The #DIV/0! error you see is nothing. Just ignore it. It is the excel function spitting an error because there are no values available for an empty cell.

Feel free to modify it and share it.
If you are having trouble let me know and I will try to help.

Download Section

Randy’s SMF Add-in
Original Portfolio Performance vs S&P 500 spreadsheet & Instructions
Automated Portfolio Spreadsheet – Old School Value Version

How to Install

edit: (view the installation guide linked above)

You have to unzip the file into c:/SMF
If you put it in any other folder it wont work.

For Excel 2003:

  1. On the Tools menu, click Add-Ins
  2. Click the Browse button and navigate to the smf add ins folder
  3. Select the add in file that you see and then click OK
  4. Click Yes to any file copy or overwrite prompts
  5. Verify the Stock Market Function add in box is checked
  6. Press OK

For Excel 2007:

  1. Click the orb and then click on excel options
  2. Click Add in
  3. Down the bottom there is a drop down list and next to it there is a move button (I think). Click on the button
  4. Navigate to the SMF add in folders
  5. select the file and then click OK
  6. Verify the Stock Market Function add in box is checked
  7. Press OK

Open the spreadsheet, enter and ticker and you will see a “calculating xx%” in the bottom right corner. You are good to go.

Note: if you download the file, copy it to a USB and then copy it back to another folder or computer, MAKE SURE that the data in the “Statements” tab is pointing to c:/SMF and not your USB drive.

  • http://www.icarra.com/viewPortfolio.php?id=2498 CUInHawaii

    Icarra.com is a great portfolio tracker. Lots of statistics and graphs. Free and pay options. Sometimes they have problems with their data feeds and the quotes are always the prior day close, but it seems to be getting better all the time.

  • http://oldschoolvalue.blogspot.com Jae Jun

    I use Icarra myself :)
    If you are curious, you can search for Old School Value and you can see how Im doing.
    It’s a great service. But I like to be able to update things instantly rather than having to wait the next day to see what happened.
    The SMF plugin is also very powerful. You are able to create graphs, arrays, quotes, historical quotes etc etc to build a powerful analytical spreadsheet, saving you lots of time.
    But Icarra is a great service. Highly recommended! My blog is listed there as well:+:

  • DoubleD

    Hi,
    For some reason I can’t download the automated version of the Excel spread sheet.
    Please provide me with a link.
    Thanks
    doubled

  • http://oldschoolvalue.blogspot.com Jae Jun

    Hi DoubleD,
    The site and all the files are down temporarily.
    Google blocked my files for no reason. I cant get access to it myself. Also, my laptop was stolen so I have to either work off an older version or wait till google lets me access my own files again.

  • http://oldschoolvalue.blogspot.com Jae Jun

    You can download all the files again. Google apologised for the inconvenience. I apologise to those inconvenienced.

  • DoubleD

    Thanks Jae,for uploading all the files back. Any luck finding your laptop ? Good luck with your laptop search.
    DoubleD

  • Annie

    can’t get the SMF add in to work- the zip file is automaticall extracted? What am I doing wrong here?

  • http://oldschoolvalue.blogspot.com Jae Jun

    make sure that the files are extracted to “smf”.
    i.e. all the files should be in c:/smf/
    There should be no files in a subdirectory such as c:/…./smf/smf/
    hope that works.
    Good Luck

  • Mervin

    Hi Jae,
    Great spreadsheet. I was wondering how can i configure this to analyze australian stocks?
    cheers,

  • http://www.oldschoolvalue.com Jae Jun

    Hi Mervin,
    The spreadsheet will only work with with US stocks (or stocks listed in the US markets) because the function is referencing a US website called morningstar.com which only has information for US stocks.
    If you want to do it for Australian stocks you would have to rewrite the “statements” tab to point to information in an Australian stock database like ASX or something else.
    Sorry I cant help on this one.
    Thanks,
    Jae

  • http://www.oldschoolvalue.com Jae Jun

    Mervin,
    Sorry, I seem to have referred to the wrong spreadsheet. If you want to do Australia stocks, just point to an Australia site which displays stock prices. If you want specifics email me and let me know.

  • Rtan

    I installed this but it doesn’t auto update info when I enter it into xl (like the stock prices after I enter the tickers or the spy value). It’s Excel 03. I installed the addin as instructed to correct directory…. ideas???

    TIA,

    Ryan

  • http://www.oldschoolvalue.com Jae Jun

    Hi RTAN,

    The SPY value is automatic so you should not enter anything in that box.
    All you have to enter is the ticker and the transaction values and the rest should get updated automatically if done properly.

    Are you getting errors such as #name! ?

  • Ryan

    Howdy Jae,

    What all values do I need to fill in? Should I check the addin from the start or wait till all info is entered and apply it? I put in the info and it doesn’t update though it appears to have done so for the pre-entered tickers….

    TUA.

  • http://www.oldschoolvalue.com Jae Jun

    You should only have to fill in the ticker and your purchase price, date and quantity. Everything else is then automated.

    If the default tickers show values, it works properly. Try typing over the top of it and then see what you get.

  • Ryan

    If I use the presets, it works…. But If I try to enter a third stock, even with all the info it will not go….. What’s going on?

    TIA.

  • http://www.oldschoolvalue.com Jae Jun

    I see what the problem is. When you enter a new ticker on a new line or add a new line, you have to drag the formulas from the above cells.

    The easiest way for you to do it is just copy the entire row of one of the preset and then paste it onto the new line.

  • Ryan

    Good news, I managed to work it by copying the entirety of the first two cells and replacing all the pertinent info with my own…. BUT, I have another question. Is it capable of backtracking? Like If I change the “Date of Evaluation” to 1/14/09 then will it back track and put in the values from that day as the current values? I tried just changing the day and I don’t seem to have been successful…. Thanks again for all your assitance.

  • http://www.oldschoolvalue.com Jae Jun

    I am assuming you are talking about the value of SPY on the day you purchased? If so, yes, as long as you enter the “Date of Purchase” the “SPY closing price on date of purchase” will update to the date.

    You enter the data in manually into Columns A-E and then everything after that is automatic.

  • Ryan

    what about the “current price” on the individual stocks? Anyway to make them reflect the day entered? For example, I want to compare the purchase amounts of January 7th to the prices of them all on January 14th.

  • http://www.oldschoolvalue.com Jae Jun

    The Current price will only give you the “current” price. i.e. the latest price. If you bought on Jan 10, today is Jan 27 and you want to compare with price on Jan 20, you will have to make modifications yourself.

  • Ryan

    gotcha… thanks for your help.

  • Ryan

    OK, new issue. When I open it up with my stocks in the current prices they are not today’s prices. Some aren’t even anywhere in the entire range of prices from Yahoo finance…..Ideas?

  • Ryan

    by range I meant today’s price ranges.

  • http://www.oldschoolvalue.com Jae Jun

    Ryan,
    I sent you an email.

  • JeffB

    re: the auto extraction feature…

    Are there any plug-ins that would work with Open Office? or perhaps Office ’97?

    Thanks.

  • http://www.oldschoolvalue.com Jae Jun

    I don’t think there is anything for Open Office.
    Also not sure about whether it will work with Office 97. After all, it is product that is over 10 years old. Office 2002 or even 2003 can be purchased for as little as $10 these days.

  • Niv

    I have donwloaded the automated portfolio spreadsheet. How shall I change my index to STI (Straits Times Index Singapore ETF) instead of SPY (S&P500 ETF)

  • http://www.oldschoolvalue.com Jae Jun

    I don’t think STI is an ETF listed on the US exchange. It won’t work because there will be no price information for the ETF. If you find something else, just rename “SPY” in the formulas with whatever you want to use as the comparison.

  • Vincent

    Is there a way I can get the automated portfolio spreadsheet to work on Excel operating on a Mac OS? Thanks.

  • http://www.oldschoolvalue.com Jae Jun

    The only way to do it is to load windows through a virtual machine with windows office. Otherwise, there is no way.

  • ZC

    Was wondering if there are any spreadsheets available that can calculate the annualised returns?

  • http://www.oldschoolvalue.com Jae Jun
x
Receive Email Tips and Strategies on Achieving Higher Stock Returns

Bonus: FREE Set of 9 Investing Spreadsheets