If you want excel, it gets complex to view you portfolio, record your transaction and record you share price history on top of displaying the data in nice graphs and charts.
For a simple solution I suggest 4 tabs.Detail tab:
Investment Account, Investment Symbol, Date, Shares, Cost Per Share, Total Cost.
You would record all you transactions here. I would use positive values when buying shares, negative values when selling shares. Current Portfolio Tab:
Investment Account, Investment Symbol, Total Shares, Total Cost Per Share, Total Cost, Market Value Per Share, Total Market Value
You then use SUMIFS formula (Excel 2007) to get you data from the detail tab to the portfolio tab. If your handy in excel you can get stock quotes to populate in a third tab and use that data to drive you Market Value Per Share cell in you portfolio tab. It would be helpful if this tab had a portfolio as of date so you could change it to see earlier time periods.Portfolio Snap Shot Tab:
Because anything else I can think of would require complex formulas and perhaps some VBA coding, just take a snapshot of your portfolio at a specified interval (monthly) and put it on this tab with and additional date column that holds the date snapshot was taken, you can use this tab to generate your graphs and charts of historical performance.Historical Fund Prices Tab:
Date, Symbol, Closing Share Price
Use this to populate your portfolio tab, I typically would take the closing share price for the maximum date on this tab that is less than my portfolios 'as-of date' for the symbol I am interested in. That way if I am too lazy to enter daily numbers it will get my most recent share price and use that.
If you like excel and want to set this kind of thing up that's cool, but there are free resources that could get you the same or most likely better. Does the institution that holds your investments provide a summary or access to a web site to view that information? It get more complicated if you want to track distributions & reinvested distributions or you want to capture specific performance measures. I used to do this to but have since opted for a simplified indexing portfolio that is rebalanced twice a year.
Buy low, sell high