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.
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)
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)
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.