(This insta-blog is for folks with no spreadsheet experience.)
I have read tons of SA articles by numerous authors. My experience with SA has been like hanging out with some guys playing poker, smoking cigars speaking sage-like about their trading days in the dimly lit basement. Nothing but experience slipping from their tongues as they dip freshly cut cigars in cognac...
But seriously, there should be more cognac around here. I've learned a ton from everyone here, so a huge thank you to all the contributors and commentators. It has been edifying to say the least and you all have helped shape my investing ideas and methods for the better. To wit, I was attempting to come up with a way to give back and from a stock perspective I've got nothing. Let's be honest... no one should be taking advice on stocks from me!
That said, maybe from a tech point of view I can contribute. I've seen folks talking about spiral bound notebooks and spreadsheets to keep track of stocks. One great (and free!) option I haven't seen mentioned is Google Spreadsheets.
(No, I'm getting paid for this by Google... and no I'm also not a share holder...)
Google spreadsheets, simply put, rock. You can link in quotes and sundry other interesting items to a cell and have them updated as frequently as if it were a ticker lookup on google.com/finance It's amazing really.
This link will take you to a Simple Dividend Stock Spreadsheet.
I have randomly chosen the following tickers just because: (F), (O), (RCI), (TMP), (WEC), (WMT)
When using the spreadsheet, there are a couple of simple things to do manually. If anyone has suggestions on how to get them automated please feel free to chime in.
1. On the dividends tab we want to get things enter in a chronological order, most recent dividends received at the top. NOTE: If you do not make new entries into the bottom of the sheet in the available blank cells, other parts of the spreadsheet won't see them. So, take these steps...:
a) Input the dividends as they get paid *at the bottom* of the tab named: "Dividends".
b) Hold down the "CTRL" key and press the letter "A" at the same time. This will select everything on the sheet.
c) Click on the "Data" menu item and chose "Sort Range..." - it's the 5th one down.
d) Chose to: sort by "Column B" and check off the "Z -> A" radio button.
e) Click the blue "Sort" button.
2. Sum up the dividends at the end of the quarter and put them into the first tab named: "QuarterlyIncome". To do this easily follow these simple steps:
a) Go to the "Dividends" tab. Everything should be sorted by date, descending. If not, follow the steps right above this to get that done.
b) Single click into the "Amount" column of the cell that ends the quarter.
c) Hold down the Shift key.
d) While holding the Shift key down, press the Down Arrow key until you reach the cell for the amount at the beginning of the quarter. (Release the Shift key.)
e) Look in the very bottom right hand corner of the spreadsheet down by where your system clock is and you should see the sum of all the highlighted cells.
f) Remember the sum. Click on the "QuarterlyIncome" sheet and enter the sum into Column C. (You will have to scroll down to row #24 for that data to be visible.)
g) Scroll back to the top of the sheet to see your updated graph.
That is pretty much it for this basic how to. This should be enough to get you started. If you are afraid of screwing things up, you can always create a new copy of the spreadsheet. You can also go back to a prior version. Under the "File" menu there is a "Revision History" option. They've done a really good job with this so it is easy to undo mistakes.
The real power of this format comes in the shape of the "B" column on the "Stocks" tab. It keeps everything automatically updated!
I am by far and away no expert but let me know if there are questions and I'll do my best to answer them.
~Zalach
Some notes:
- Under the "View" menu I've chosen to "Freeze 1 row" and "Freeze 1 columns". Makes things easier as you scroll around.
- You will note that column E (% of Portfolio) is Red for (WMT) and Green for (F). This is an example of conditional formatting and is useful to highlight things of note that disappear in a sea of numbers. In this case, over-weight or under-weight positions. To change this formatting, click the letter "E" at the top to highlight the entire column. Then click the "Format" menu item and choose "Conditional Formatting...". Should be self-explanatory after that!
- I've used more conditional formatting on columns N and O to highlight the fact that RCI has been letting me down! To modify, click the "N" column, then while holding the shift key down, click on the "O" column. Same as above, chose the "Format" menu item and then "Conditional Formatting..."
Random facts about the Google Spreadsheets:
- All stocks were "purchased" on 1/4/2013
- All data is from Google finance. In reality, I use my monthly broker statements to cut+paste results from excel into the Google spreadsheet to save manual typing and then just do the sorting thing to get them in the proper descending order.
- Google has a hard limit of 1,000 cells with calls out for Google Finance data. As a result, I've chosen to derive as much of the data as I can. In this example I'm only grabbing Price, but there are a ton of things available. Here's a full list.
- Google has a hard limit of 400,000 cells per spreadsheet. If I hit 400,000 dividend payments, I can probably afford the pay version.
Disclosure: I am long F, O, TMP, WEC, WMT, RCI.