**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**