Should you bother picking stocks? A simple Excel Spreadsheet By thefirelane in Technology Wed Mar 29, 2006 at 02:15:58 AM EST Tags: You Know... (all tags)
	When investing in the stock market, tracking one's returns accurately is the most important thing no one does. This article will show how regular investors can track their returns against the S&P 500 index. Any other index could be used. The instructions in this article give the steps necessary to create an excel spreadsheet that will show two things: the total return on investment of all stock transactions, and the hypothetical return of those same transactions if the funds were instead invested in an exchange traded index fund (in this case, SPY). Many investors are not aware of what this delta is, and therefore they have no method to measure themselves as a stock picker. It could very well be that they are wasting their time and money when they could simply invest in an index fund and achieve greater performance.

Step 1: adding all transactions This is the most tedious part, but it is essential to capture all transactions in order to get an accurate picture. Create an Excel spreadsheet with a row containing the following values as headings: Ticker Date Purchased Date Sold Shares Bought Purchase Price Current, or Sold Price Amount Invested Percent Change Dollar Change S&P Change S&P Dollar Change Difference For convenience, I have already created an example spreadsheet Now you must manually type in the following information from your records for each stock: Ticker Date Purchased Shares Bought Purchase Price Note: some stocks, if purchased at different dates will have this information across more than one row: such as this If you have sold the stock, add the following information manually: Date Sold, Sold Price. In the example spreadsheet, I've added a couple stocks, two of which are currently held: PTR, EBAY and one which was sold: ARMHY. If you have not sold the stock, ignore the current price cells for now. Note: currently purchase price has to be adjusted for any stock splits by dividing it (see eBay in example spreadsheet) Step 2: Automatic formulas Next we add the formulas that are automatic across all transactions. In the columns listed, enter the formulas given. Apply these formulas across all rows by clicking the lower right corner of that cell and dragging it down. Amount Invested: =D2*E2 Percent Change: =((F2/E2)*100)-100 Dollar Change: =(G2*((H2+100)/100))-G2 S&P Dollar Change: =(G2*((J2+100)/100))-G2 Difference: =K2-I2 Your spreadsheet should now look like this Step 3: Adding the S&P 500 First, go to this page. In the form, enter the date of your first stock purchase. This date is needed in order to compare the returns between your investments and if you had made that same investment in the S&P 500. Make the end date today's date. Click on 'download to spreadsheet'. Open this .csv file in excel and select all the columns. Copy and paste this information into a new worksheet in your current excel file. Your spreadsheet should now look like this Step 4: Vlookup(), your friend Now, we can compute the S&P 500 change value (for now, just the sold stocks). To do this, we use the VLOOKUP function. VLOOKUP searches for a value in the leftmost column of a table and then returns a value in the same row, in a specified column. Basically, we will use this to look up the value of the S&P 500 at the date we bought (and sold) a stock. For your sold stocks, set this column to the following formula (adjusting for row): =((VLOOKUP(C2,SP500!A:G,7,)/VLOOKUP(B2,SP500!A:G,7,))*100)-100 This formula takes the values in Date Purchased, and Date sold, and then scans through the S&P 500 historical prices until it finds those dates. When it finds those dates, it returns the adjusted close, which accounts for any dividends and stock splits. Step 5: getting the current prices Now you must run a web query. You will want to run a saved query. Windows: Open, ProgramFiles/MicrosoftOffice/Office11/Queries/MSN MoneyCentral Investor Stock Quotes.iqy Mac:Insert a new worksheet, and call it 'portfolio'. This worksheet is where you want to add your web query. Go to: Data, Get External Data, Run Saved Query... MSN MoneyCentral Investor Stock Quotes Now add the stock tickers that you have not yet sold. Be sure to also include SPY! Click 'use this value for future refreshes'. For windows users this will open a new spreadsheet. Rightclick the worksheet name, and move it to the current spreadsheet you are working on. Mac users should have the portfolio opened into their new worksheet. You will now have a nice portfolio in an excel spreadsheet. This can be updated with current prices by going to: Data:Refresh Data In the transactions worksheet, for stocks which were not sold, set their value (column F) equal to the current price in the MSN money download chart. Example formula: ='MSN Money Central Stock'!D4 Under the column 'S&P change' use Vlookup as described in step 4 to compute the percentage change between the purchase price, and recent price of the S&P500 in the web query Example: =(('MSN MoneyCentral Investor stock'!D6/VLOOKUP(B2,SP500!A:G,7,))*100)-100 Almost done Now the column 'Difference' accurately reflects the difference between your actual transaction, and what you would have made if you put the equivalent amount of money in SPY. In this case negative numbers are good As it means you would less money if you invested in the S&P500 index. As a final touch, insert one more row under each stock that has paid a dividend. In the example file, I included a 100 dollar dividend from PTR. This value must be added as a negative in order for the math to work Reporting the final result Create a new worksheet. with the following text in column A Total Amount invested Total Gain % Gain S&P500 Gain S&P 500 % Gain If I invested in S&P 500 In column B, they should have the corresponding formulas: =SUM('all transactions'!G:G) =SUM('all transactions'!I:I) =B2/B1*100 =SUM('all transactions'K:K) =B4/B1*100 =SUM('all transactions'L:L) Again, for the final value, negative numbers are better. It means you picked things better than simply investing in the S&P 500 index fund. Sadly, for most people who read this, this number will be positive. Closing thoughts There are still a couple inconvenient things that must be done with this excel spreadsheet. For instance, everytime a stock is sold, the S&P500 historical data must either be re-downloaded, or entered by hand. Since most invdividuals interested in this tracking tool are long term investors, this should not be too frequent. In addition, all the final difference number does not include several important considerations: You must consider the comisison for trading an ETF. For this chart, commission is not accounted for, as it would be equal for both transactions. However a comparison between trading stocks versus an index mutual fund (where there is no charge to invest further fund) might skew the results in favor of the mutual fund. Lastly, even if you are one of the fortunate investors to come out ahead of the S&P500, you can now put a hard number on how much your time has been worth. Undoubtedly this has required research, stress, newspaper subscriptions, and time. Is the total amount of time worth the number displayed in the excel spreadsheet? This depends on you.

Poll

You vs. S&P500? I don't know 5% I don't know, and won't do this to find out 18% I'm a day trader, this is too much work 0% I don't do stocks 40% S&P beat me by 50%+ 0% S&P beat me by 40-49% 0% S&P beat me by 30-39% 0% S&P beat me by 20-29% 0% S&P beat me by 10-19% 0% S&P beat me by 0-9% 8% I beat the S&P by 0-19% 8% beat the S&P by 20-29% 8% beat the S&P by 30-39% 2% beat the S&P by 40-49% 0% beat the S&P by 50%+ 8% Votes: 37 Results | Other Polls

Related Links

SPY example spreadsheet such as this this this page. this [2] Now add the stock tickers that you have not yet sold this will open a new spreadsheet Rightclick the worksheet name move it to the current spreadsheet you are working on Also by thefirelane