All About Interest

One man's journey to financial independence.

  • About
  • Get Started
  • My Portfolio
  • Dividends
  • Options
  • Real Estate
  • Goals
  • Product Reviews
    • Brokerage Accounts
    • Lighting (LED)
  • Contact

Simple Google Docs Stock Portfolio

October 28, 2015 by All About Interest 19 Comments

I’m still getting emails on what I’m using to pull my stock information automatically into my Google spreadsheet.  I previously posted a couple of articles that were well-received on creating a Google Docs portfolio. You can find those articles below.  I’m currently using a simpler method since I was having issues with Google Docs pulling in all the information, sometimes I’d get a cell that said “loading”.  This might be fixed with the new Google Docs updates but I already switched over to this new method.

I’m going to give access to a sample google docs portfolio at the bottom of this article.

Previous Articles:

Using Google Docs For Your Portfolio

Automatically Import DGR from CCC Sheets into Google Docs

 

I’ve created a sample portfolio based on the first 10 stocks in my current portfolio:

Portfolio_first_10

The ticker is the only thing I have input mannually, that is, column A.

I hold stocks in multiple brokerage accounts and I’ll go over how I’m pulling that information from other sheets first.  The columns calculated from other sheets are B-G.  Feel free to skip to H if you want the new formula I’m using for the rest of the columns and keep all your stocks in one account.  I have three other tabs that I keep separated by brokerage company.  Those tabs are etrade, schwab and sbuilder.  The columns are in a similar order on those pages.  Basically my main page is summing and averaging these shares together since I own the same stocks in multiple accounts.

Cell B2:  =SUMIF(etrade!A$2:A$21,A2,etrade!B$2:B$21)+SUMIF(Schwab!A$2:A$12,A2,Schwab!B$2:B$12)
+SUMIF(SBuilder!A$2:A$32,A2,SBuilder!B$2:B$32)

Cell C2: =SUMIF(etrade!A$2:A$21,A2,etrade!G$2:G$21)*sumif(etrade!A$2:A$21,A2,etrade!S$2:S$21)
+SUMIF(Schwab!A$2:A$12,A2,Schwab!G$2:G$12)*sumif(Schwab!A$2:A$12,A2,Schwab!S$2:S$12)
+SUMIF(SBuilder!A$2:A$32,A2,SBuilder!G$2:G$32)*sumif(SBuilder!A$2:A$32,A2,SBuilder!S$2:S$32)

Cell D2: (this is the % away from my cost basis, the colors are from conditional formats which are pretty self-explanatory): =(H2–C2)/C2

Cell E2:  =B2*H2

Cell F2: =E2/E$13

Cell H2: =ImportData(“http://finance.yahoo.com/d/quotes.csv?s=”&A2&“+”&A3&“+”&A4&“+”&A5&“+”&A6&“+”&A7&“+”&A8&“+”&A9&“+”&A10&“+”&A11&“&f=l1wj6rdqyj1p“)

The part in red is a series of tags that will allow you to pull the information you want from yahoo finance based on this chart below and can be modified to your liking:

 

Yahoo_Tags

I’ve also got a tab that keeps up with your dividends and looks like this:

sample_dividends

I’ve also included tabs that keep up with your portfolio weights and have a graph based on sector weights, geographic diversification and market weights.  Here’s the sector weights graph below for this sample portfolio.

sample_sector_weights

 

Without further ado, here’s the new simplified version of my google docs portfolio:

Link to sample portfolio

 

Feel free to download and modify the sheet as needed.  I welcome any comments or suggestions.  If you do find this helpful, I encourage you to like my Facebook page and follow me on Twitter.

 

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Google+ (Opens in new window)
  • Click to print (Opens in new window)

Filed Under: Articles

Comments

  1. DividendChimp says

    October 28, 2015 at 1:27 pm

    Thanks for sharing. It amazes me we are still having to pull data from Yahoo into Google. I’ve looked at roughly 30 of these lists from different sources but I don’t see “next earnings report” do you know what “letter” links to the next earnings report date? I haven’t been able to find it be process of elimination.

    Best,
    Devin

    Reply
    • All About Interest says

      October 28, 2015 at 2:30 pm

      Hi DividendChimp,

      No problem. It seems like this method pulls the data I need quicker and more efficiently than other methods I’ve tried.

      From a little research, it appears the next earnings date is not included in the Yahoo API. There’s some info on Stack overflow about it. It looks a little complicated to build your own API to pull in the info. I could do it on Excel but no idea how to get it in Google Docs.

      http://stackoverflow.com/questions/27721921/using-webservice-function-in-excel-to-pull-next-earnings-date-from-yahoo-finan

      Maybe that helps. Thanks for stopping by!

      Reply
      • DividendChimp says

        October 29, 2015 at 4:20 pm

        It is an efficient way to pull data. I should have said – I can’t believe that Google Finance hasn’t improved over the years and we are still using Yahoo to pull into Google.

        Thanks for the link. I’m just learning how to make APIs, I was able to make one to pull next earnings but it was specific to one ticker, I’m not sure how to get it to change tickers, if I reference the cell containing a ticker it no longer works in Google sheets.

        I’m just going to pull it from another google sheet otherwise I’m going down the rabbit hole with building APIs

        Thanks for the response and link

        Devin

        Reply
  2. JC @ Passive-Income-Pursuit says

    October 28, 2015 at 7:32 pm

    Thanks for sharing this. Once I get some more time I want to redo my portfolio spreadsheets because they could be simplified and cleaned up compared to what they currently are. And I’m a fan of automation so consolidating the different accounts into one spreadsheet automatically is a great idea. I’ll have to give it a look around and see what I can do to improve my own tracking. Thanks again.

    Reply
    • All About Interest says

      October 29, 2015 at 11:50 am

      JC,

      No problem. I figured it might help out a few people. I love having everything automated. It really saves a lot of time and is so much more efficient. If you need help with formulas let me know, I’ve gotten pretty good at them.

      Cheers!

      Reply
  3. DivGuy says

    October 29, 2015 at 1:54 pm

    I have nothing as precised as this. I don’t follow my dividend stocks more than once or twice a year (unless there are some big news). However, I know many investors that enjoy doing those and I think yours are pretty lean compared to many! Good work!

    Cheers,

    Mike
    DivGuy recently posted…I’m Done with my FearsMy Profile

    Reply
    • All About Interest says

      November 4, 2015 at 11:19 am

      DivGuy,

      Thanks! I do enjoy keeping up with my stocks. I also love anything that can automate things for me. This portfolio is relatively easy to keep updated now.

      Take care!

      Reply
  4. Alex Gregory says

    November 2, 2015 at 8:11 pm

    it says view only for me and i have to requet edit acess how do i down this for myself ?

    Reply
    • All About Interest says

      November 4, 2015 at 11:19 am

      I emailed your back, let me know if you stil can’t download it.

      Reply
  5. FreewillFinance says

    November 3, 2015 at 11:12 am

    Really appreciate you posting this up! I consider myself a bit of a spreadsheetaholic so I will definitely work on implementing this to my blog.

    -FwF
    FreewillFinance recently posted…Income/Expense Report October 2015My Profile

    Reply
    • All About Interest says

      November 4, 2015 at 11:21 am

      FreewillFinance,

      I’m happy to give back to others as I’ve learned so much from some of my fellow bloggers. I love spreadsheets! I’ll stop by to check it out. I’m glad it’s helpful.

      Cheers!

      Reply
  6. Laurence says

    January 16, 2016 at 4:42 pm

    Hi,

    Great site!! Any chance of using your spreadsheet models out of the box?
    Or must we re-configure everything?

    Thanks,
    Laurence

    Reply
    • All About Interest says

      January 16, 2016 at 8:04 pm

      Laurence,

      Thanks, I’m happy you found it useful! I’m not exactly sure what you are asking? The spreadsheet is made to download and tweak to your own portfolio. You shouldn’t have to change any formulas unless you want different values from Yahoo. I actually just went in and highlighted areas that you can edit to make it a little simpler for those not too familiar with spreadsheets. The only things you really need to change will be your specific holdings, you need to put them on the DG tab as well as one of the spreadsheet tabs. You should be able to rename tabs without trouble also but I haven’t tried.

      If you have any other questions you can post here or email me, I’d be happy to help.

      Cheers!

      Reply
  7. CNY says

    January 16, 2016 at 5:39 pm

    Thanks very much for this. I’m transitioning from a pre-retirement to a post-retirement portfolio and therefore making frequent changes (it’s OJT!). Since the post-retirement version has a strong dividend focus I’ve been searching for automated ways to track both dividends and total return. Your technique will help a lot with the dividend side and I should be able to build the total return side from that.

    The formula in H2 pulls in a matrix. This is clumsy for me since I’m making frequent additions. I might try to modify it to be effective for a single line or for a cluster of line items, say 5 at time.

    Can you advise a reference source that gives a tutorial on how to build these commands?

    Was very helpful. Thanks again for posting it.

    Reply
    • All About Interest says

      January 16, 2016 at 8:25 pm

      CNY,

      You’re welcome and I’m happy it’s helping. First congratulations on post-retirement! I hope to meet you there someday.

      Yes, it’s a matrix that you can tweak to any order and any number of hash tags that you like. I kept it simple for people that don’t want a lot of information but you are able to modify it as you need to. You really shouldn’t have to do much modification once you get it set up. If you do add additional rows for more stocks I suppose you need to add in the additional rows to the formula but that’s about it. If you are just adding shares to the same stocks you own, you only need to fill in the specific portfolio tabs with new number of shares and new cost basis.

      If you have any more specific questions you can reply here or email me.

      Thanks for stopping by!

      Reply
  8. Jason says

    August 6, 2016 at 12:37 pm

    How do I add more rows as I have more stocks I would like to enter. I have tried addending Column H2 but cant seem to get it to work. Also thank you for the spreadsheet it is exactly what I am looking for.

    Reply
    • All About Interest says

      August 19, 2016 at 12:22 pm

      Jason,

      Send me an email and I’d be happy to help you.

      Cheers!

      Reply
  9. linda says

    July 16, 2017 at 12:54 pm

    thanks for sharing googlesheet.

    I download it and in Cell L of DG tab, it seems you manually into yearly dividend? because I don’t see the formula to grab it from yahoo website.

    or is it still working? where did you put in the formula below?
    =REGEXextract(REGEXreplace(index (importhtml(“http://finance.yahoo.com/q?s=”&A2&”&ql=1″, “table”, 3), 8, 2); “[()]”; “”) ; “([^/]*) “)

    thanks

    Reply
    • All About Interest says

      July 17, 2017 at 10:01 am

      Cell H2 contains the Yahoo formula to pull in the data. Let me know if you have any more questions.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CommentLuv badgeShow more posts

About Me

I'm a 39-year-old dividend growth investor, wanna be real estate mogul, entrepreneur and dad. Follow me on my journey to financial independence!
$3,130 / $6,000

Total Monthly Passive Income / Goal

Follow by Email

Tweets by @AAInterest

Follow Us On

Follow Us on FacebookFollow Us on Google+Follow Us on TwitterFollow Us on RSS

Most Popular Posts

  • How to Calculate Your New Vodafone (VOD) Cost Basis
  • DIY – How to Install a 3-Way Programmable Light Timer
  • Using Google Docs for Your Portfolio
  • I’m Replacing All My Light Bulbs with LED’s
  • Are Baseball Cards a Good Investment?
  • Milestone – $1000 a Month in Dividends!!!
  • Simple Google Docs Stock Portfolio
  • My Business Plan – All About Interest
  • Dividend Growth vs Dividend Yield
  • Motif Investing Review

Categories

Disclaimer

I am not a licensed financial professional. I created this site to be informative and entertaining. No purchases I make are recommendations to buy those particular equities. I'm not liable by any party for losses you might incur. All investments are subject to losing money and you should consult a financial professional before making any investment decisions.

© 2019 · All About Investing · Site by Nuts and Bolts Media · Privacy Policy