Seeking Alpha

Zalach's  Instablog

Zalach
Send Message
Random bio facts...; Ancient Historian cum Oracle DBA/Linux Admin with excessive life lost to everquest finally turning downtime productive by playing the role of novice investor... in real life... /ponder
  • Google Spreadsheets For Beginners 18 comments
    Apr 18, 2014 11:34 PM

    (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: (NYSE:F), (NYSE:O), (NYSE:RCI), (NYSEMKT:TMP), (NYSE:WEC), (NYSE: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.

Back To Zalach's Instablog HomePage »

Instablogs are blogs which are instantly set up and networked within the Seeking Alpha community. Instablog posts are not selected, edited or screened by Seeking Alpha editors, in contrast to contributors' articles.

Comments (18)
Track new comments
  • Eric Landis
    , contributor
    Comments (1187) | Send Message
     
    Thanks for the post. I use google docs for tracking my portfolio as well and agree that it works great. Looking forward to playing around with some new ideas from your link.
    18 Apr 2014, 11:52 PM Reply Like
  • Zalach
    , contributor
    Comments (105) | Send Message
     
    Author’s reply » Thanks Eric - I'm glad someone found it useful!
    19 Apr 2014, 12:04 AM Reply Like
  • Eric Landis
    , contributor
    Comments (1187) | Send Message
     
    I use google docs to track my 401k and used it to make the graphics for my latest article http://seekingalpha.co...

     

    I've already implemented the P/E into another column and plan on going through FAST Graphs to add the historical PE in a column next to it for quick reference when I'm ready for my next buy.

     

    Thanks again.
    19 Apr 2014, 12:38 AM Reply Like
  • Zalach
    , contributor
    Comments (105) | Send Message
     
    Author’s reply » Very cool!

     

    Adding the historical P/E is a great idea. hmmm have to ponder what time frame to use.

     

    I like how you've handled the "Invested Capital" vs "Intial Basis Per Share". I've waffled in my spreadsheet a couple times on how to track it. Changed it around several times and I'm still not real happy with it. I've been using a TCO (total cost of ownership) vs. market value of late.

     

    Part of my problem is I DRIP certain stocks because of the account they're in (high commission). These tend to be my slow growers like utilities. For others I take the piled up dividends and pick the weakest link to buy. Then throw in growth stocks and options and rolling all that up gets fun trying to keep things neat and tidy.
    19 Apr 2014, 01:11 AM Reply Like
  • Eric Landis
    , contributor
    Comments (1187) | Send Message
     
    It does get difficult to track your return when you aren't consistent across the portfolio. My broker doesn't reinvest dividends with foreign holdings so right now POT income gets put into my cash pool while I DRIP the rest. Guess there was a reason why I wanted only U.S. companies when I first built the portfolio!

     

    I also have an old IRA account that doesn't have any new money coming in. So in that one I bought a basket of higher yielding stocks and I am planning on pooling dividends until I have enough to start a 1/2 position in a new stock.

     

    So many ways to invest and track things, thanks again for sharing.
    19 Apr 2014, 01:24 AM Reply Like
  • maybenot
    , contributor
    Comments (4457) | Send Message
     
    Zalach -- thanks for providing this. I have been wanting to do this. Doesn't mean I will. I said, "I have been wanting..."

     

    But, I think I like what I see here. If I have questions I may get back to you for answers.

     

    I just don't want to do a bunch of work setting it up and then realizing I will never really use it much.

     

    But, most of all, thanks for sharing -- it is what makes SA so unique.
    19 Apr 2014, 12:22 AM Reply Like
  • Zalach
    , contributor
    Comments (105) | Send Message
     
    Author’s reply » I'm very happy to! I know the feeling, ha! Ask away, always glad to help.

     

    Indeed... I am quite happy to have stumbled onto this corner of these tubes and pipes, now referred to as the internet, several years ago now...
    19 Apr 2014, 12:29 AM Reply Like
  • BigIslandBum
    , contributor
    Comments (463) | Send Message
     
    Zalach,

     

    Thanks for sharing this info, it was something on my "to do" list. I us Google Spreadsheets for tracking my portfolio and use Google Script for importing data from Yahoo and M* into my portfolio sheets.
    20 Apr 2014, 03:25 PM Reply Like
  • Zalach
    , contributor
    Comments (105) | Send Message
     
    Author’s reply » Sure, happy to! I wish the formatting was a bit better for easier reading but oh well.

     

    What types of scripts do you use, and what data are you importing? There are some limitation to the googlefinance calls. For example, I'd love to be able to calculate PTI's PAAY number for each stock but there's no easy way to get that historical data.

     

    That said, Google Spreadsheets are still pretty slick, you can even share data between spreadsheets. (use the ImportRange function) This would allow you to roll-up different types of data into one master spreadsheet for example.

     

    I might post a more detailed one in a bit if I have some time.
    20 Apr 2014, 05:02 PM Reply Like
  • BigIslandBum
    , contributor
    Comments (463) | Send Message
     
    I wrote a library of Google Apps Script functions for getting a variety of stock metrics. Pretty much anything on the M* Key Stats tab, M* star rating, M* credit rating. Yahoo historical data, dividend, beta, future eps, ex-div day, pay day, analyst rating, etc.
    21 Apr 2014, 02:14 PM Reply Like
  • Zalach
    , contributor
    Comments (105) | Send Message
     
    Author’s reply » Wow that's awesome... nicely done!
    21 Apr 2014, 05:46 PM Reply Like
  • Lovethosedividends
    , contributor
    Comments (63) | Send Message
     
    Zalach, I sent you a DM but will repeat it here: you ROCK. Thanks ever so much for posting this tutorial and for being so thoughtful as to send me a DM calling my attention to it!
    5 May 2014, 09:39 PM Reply Like
  • Zalach
    , contributor
    Comments (105) | Send Message
     
    Author’s reply » You bet! Let me know if you have questions... happy to try and help...

     

    ~Zal
    5 May 2014, 10:36 PM Reply Like
  • Smarty_Pants
    , contributor
    Comments (2894) | Send Message
     
    I gather that the stock price functions you are using only work on Googledocs?

     

    I have tried using the price function on my own excel spreadsheets (I use LibreOffice Calc on an Ubuntu machine) and get an error.

     

    It appears I can create a Googledocs sheet to get the prices, which I can then copy and paste into my local spreadsheet. That's still faster than updating things manually like I've been doing.

     

    Thanks for posting this tutorial too. I'm sure it will save a lot of people a great deal of time in their record keeping process.
    8 Jun 2014, 12:46 PM Reply Like
  • BigIslandBum
    , contributor
    Comments (463) | Send Message
     
    Yep, those functions are specific to Google Spreadsheets. You may want to consider moving your local spreadsheets to Google Spreadsheets, then you don't have to "copy and paste". You would also gain the added benefit of being able to access your spreadsheets on virtually any device.
    8 Jun 2014, 12:52 PM Reply Like
  • Smarty_Pants
    , contributor
    Comments (2894) | Send Message
     
    BIB,

     

    Thanks for the clarification. I'm a bit old fashioned when it comes to maintaining my financial data. I keep it on my local computer. Copy and paste is a lot faster than manually entering each price, as I do now. So having a bulk price generator with a one step copy 'n paste transfer function is a big time saver for me.

     

    My take on the data being available from "virtually any device" would include devices operated by someone other than myself, and I don't really consider that a good thing under all cases. Besides, I would only have access when I have internet connectivity in that case. So I'm going to stick with my somewhat more secure, and always accessible, local storage for now.

     

    Thanks again for the clarification. It's always a good thing to find a better way to skin the cat.
    8 Jun 2014, 01:33 PM Reply Like
  • Zalach
    , contributor
    Comments (105) | Send Message
     
    Author’s reply » Glad you found it useful SP - and I hope others do too... saving time is a wonderful, it's the most valuable asset we have, ha!

     

    Thanks BIB for the follow up comments. That is indeed the case.

     

    SP - If you run Excel 2010 there are some API's out there where you can pull stock data directly into Excel. MSN and Morningstar both offer something. However I have used neither and can't tell you how to use them, or what they do. For older versions you can run plug-ins and macro's, but if you are security conscious then you might want to shy away from those.

     

    ~Zal
    8 Jun 2014, 09:52 PM Reply Like
  • Smarty_Pants
    , contributor
    Comments (2894) | Send Message
     
    I use Libre Office, or Office 97. Cut 'n Paste is a big improvement over what I've been doing, so no worries there.

     

    Thanks again.
    9 Jun 2014, 08:30 AM Reply Like
Full index of posts »
Latest Followers

StockTalks

More »

Latest Comments


Posts by Themes
Instablogs are Seeking Alpha's free blogging platform customized for finance, with instant set up and exposure to millions of readers interested in the financial markets. Publish your own instablog in minutes.