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:
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:
I’ve also got a tab that keeps up with your dividends and looks like this:
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.
Without further ado, here’s the new simplified version of my google docs 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.
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
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!
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
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.
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!
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 Fears
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!
it says view only for me and i have to requet edit acess how do i down this for myself ?
I emailed your back, let me know if you stil can’t download it.
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 2015
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!
Hi,
Great site!! Any chance of using your spreadsheet models out of the box?
Or must we re-configure everything?
Thanks,
Laurence
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!
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.
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!
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.
Jason,
Send me an email and I’d be happy to help you.
Cheers!
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
Cell H2 contains the Yahoo formula to pull in the data. Let me know if you have any more questions.