I actually use Apple's Numbers as my spreadsheet. This is the gist of what I do, though in reality I do it much more concisely using flags to separate out purchases this year from carry forward open positions from batches sold this year, but that would be too difficult to describe so I will detail my methodology using tables for each value I need to calculate. A table in Numbers is just a single spreadsheet (rows X columns) that records certain things needed for my reporting.
I have a table called Purchases This Year. This has a row for each batch and each row has the following columns: Purchase Date, Ticker, Quantity, Unit Cost, Brokerage and Total Cost (extended cost including brokerage). This has no entries at the beginning of the year, but every time I a make a stock purchase, I add it as a row in this table (I group by ticker, but that is only to facilitate reporting).
I also have a table called Sales This Year. That has a row for each stock sale and each row has the following columns: Sale Date, Ticker, Quantity, Unit Sale Price, Brokerage and Net Proceeds (extended sale value less brokerage). This has no entries at the beginning of the year, but every time I make a stock sale, I add a row to this table (again grouped by ticker).
I also have a table called Stock On Hand. That is the same as the Purchases This Year table, except at the beginning of the year it is populated with those purchases (or parts thereof) not yet sold at the end of the previous year. It also has 3 extra columns after Total Cost; Current Unit Price, Market Value and Lower of Cost/Market. During the year as I add items to the Purchases This Year table, I also add them to this table. When I sell a batch, I delete the relevant row from this table (or adjust the quantity for part sales or other irregularities (stock split, consolidations, etc.)).
At year end I get my Sales for the year for calculating this years Profit/Loss by totalling the Net Proceeds column in the Sales This Year table. I get my Purchases for the year for calculating this years Profit/Loss by totalling the Total Cost column in the Purchases This Year table. Both of these will be over all rows, so are the relevant totals for ALL batches purchased or sold.
At year end I also fill in the Current Unit Price in the Stock On Hand table with the year end price for each ticker. I don't do this manually as it might mean entering BHP's year end price 20 times if I have 20 unsold batches of BHP. What I do is I have a separate table (Current Ticker Price table) that just has two columns: ticker and unit price and one row for each ticker that I hold shares in. I have a Yahoo portfolio list that also has these tickers in it in the same order. I then use Yahoo's export function to down load into this table (there is an extra step that I won't go into here). The Current Unit Price field for each batch in the Stock On Hand table uses a Lookup function (I am sure Excel has it too) to pull into that field the price from the Current Ticker Price table. The Market Value is then calculated by multiplying the quantity for each batch by the Current Unit Price Field. The remaining field, Lower of Cost/Market, is got by simply comparing the Total Cost value with the Market Value and which ever is lower is replicated to that field. I then use the column total for Lower of Cost/Market across all batches for my Closing Stock On Hand value in the Profit/Loss formula.
The only thing missing is the Opening Stock On Hand value. As this MUST always equal the Closing Stock On Hand value of the previous year, I just use the Closing Stock On Hand value from the previous years spreadsheet.
Just as an aside, I can always calculate at any time the market value of my stock on hand throughout the year by downloading the Current Unit Prices via Yahoo just as I did at year end.
Also, if I wanted to change from valuing all closing Stock On Hand from Lower of Cost/Market, to either ALL at Cost or ALL at Market Value, I can simply use the column total for these values in the Stock On Hand table at year end rather than the total for the Lower of Cost/Market column.
