There's a lot going on in this spreadsheet, some explanation is located below along with instructions for use. Here's a link without the limited window below: Portfolio
The purpose of the sheet is to display publicly my portfolio, and give me quick reference to data points I commonly use. First a rundown of the columns, then some info on using it at the bottom.
Stock, Company, Entry Date, Shares: All pretty obvious even to the greenest of investors. Exited stocks will have an exit date rather than entry, the year will be set to an earlier year so I can sort all closed positions towards the bottom.
Change: From yesterday's close to the current price, how much the stock has risen or fallen.
5-Day Return: The stock's performance over the last 5 trading days.
Risk -P/L: Was used when I entered trend trades, no longer shown.
P/L $ and %: The real dollar amount, and percentage gain/loss from first entering the stock. Now that I am doing a permanent portfolio, this will show all-time P/L.
Average Entry Price: My new way of keeping track of profits easier, as I increase or lower holdings in an ETF. By using an average entry price, market value, and cost basis, I can easily keep track without a system that requires a new line for each trade.
Static Stop: Was used when trend following stocks. No longer shown.
SMA Days and Stop: Same as above.
Exit Price and Exit Distance: Same as above
Hidden Cells: There are some other hidden columns for optional display, and row 2 is hidden, as it provides the template to enter new stock trades and should not be altered.
Closed Trades are shown under the Summary section, and watched stocks are placed under those.
How to Use
If you want to use this yourself, you'll need to go to this link: Portfolio. You will then need to make a copy to your own Google Docs by clicking File, and then Make a Copy. You will then be taken to your copy.
Delete any stock position rows you don't want (probably all), but preserve all others including hidden Row 2. Getting familiar with basic controls on Google sheets is necessary, so I won't explain the step-by-step. Note that the spreadsheet will auto sort by date when a date is entered or changed.
I use dates with older years for closed positions, headings, watched stocks, to keep them all in place, so take care to do the same. You don't see the dates in some rows because of text color, but it will appear in the value bar when selected.
For entering new trades, you can click "Trade Entry" menu at the top for an option to add a new row. You may have to authorize the script I wrote for that. Then add the relevant info to the cells that have a light green column header. The yellow ones auto-populate, don't mess with them.
Cost Basis vs Multiple Trades: I know some would just adjust cost basis when altering their position sizes, and if you want a version that takes the cost basis and uses that to determine average price paid, that can be done. For my purposes, it's easier to use multiple trades.
The second sheet (labeled "Sidebar") creates compressed versions of the portfolio and a markets widget that I use on this blog. It also contains market holidays so you need them together. Publishing a range of cells is the best way to manipulate your output in this sheet to make use of it. Here is a handy link for some basic instructions that are hopefully not too out of date: Click Me.
Closing Trades: See my portfolio (full version) to see what cells to delete for closed trades. This needs to be done occasionally as there's a limit to how many Google Finance queries can be made on a single sheet. Eventually you'd run out. Luckily, none of the dynamic info is necessary for calculating exit gain and loss.
There you go, any questions let me know, and I'd be happy to hear from you if you find this useful or if you find any issues or bugs.
0 comments:
Post a Comment