Build a Stock Tracker in Google Sheets: The Ultimate Guide
ฝัง
- เผยแพร่เมื่อ 28 มิ.ย. 2024
- Ryan O'Connell, CFA, FRM shows how to build a stock dashboard in Google Sheets with live updating data using Google Finance.
💾 Purchase the file created in this video here: ryanoconnellfinance.com/produ...
👨💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
Chapters:
0:00 - Introduction
0:10 - Create a New Google Sheet
1:11 - Setup Column Headers and Format Dashboard
2:12 - Manually Input Your Stock Portfolio Info
3:28 - Color Code Spreadsheet
4:13 - Calculate Company Market Capitalization
6:24 - Calculate the Amount Paid for the Portfolio
7:06 - Get Live Stock Prices In Google Sheets
8:07 - Calculate Allocation of Portfolio
8:43 - Calculate Stock Portfolio Returns
12:03 - Conditional Formatting Based On Gains & Losses
13:47 - Add Individual Stock Allocation Pie Chart
15:17 - Add Pie Charts Based on Market Cap & Sector
19:13 - Stock Portfolio Dashboard in Google Sheets Conclusion
*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC
ALTERNATIVE TITLES
"Create a Custom Stock Tracker in Google Sheets"
"DIY Stock Dashboard: How to Build Your Own"
"Track Your Portfolio: A Step-by-Step Guide to a Google Sheets Stock Dashboard"
"Maximize Your Investments with a Google Sheets Stock Tracker"
"Building a Stock Dashboard in Google Sheets | Live Updating Data"
"Build a Stock Dashboard in Google Sheets: The Ultimate Guide"
"Effortlessly Monitor Stocks with this Google Sheets Dashboard Tutorial"
"Get a Bird's Eye View of Your Investments with a Google Sheets Stock Dashboard"
![แฟนแนวใด๋ - ยูริ โตเกียวมิวสิค [OFFICIAL MUSIC VIDEO ]](http://i.ytimg.com/vi/4_XVMuhhbsU/mqdefault.jpg)
💾 Purchase the file created in this video here: ryanoconnellfinance.com/product/google-sheets-stock-dashboard/
👨💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
the link does not work.
The link will work now! I appreciate you letting me know Francis
Wow, I bought the spreadsheet and the link does not work. Very disappointing@@RyanOConnellCFA
Great video! Super well explained. Thanks!
Glad you enjoyed it!
Great layout, easy to follow. I would love a similar Googlel-sheets layout for "Dividend ETF" purchases, to track Dividen Yield, Expense Ratio, and other variable pulled from "Google Financials" just like the "Current Price" gets pulled on this worksheet. Thank you, Ryan, very informative.
Great suggestion! I can look into that in the future. Thank you!
Excelente video. Muy bueno. Claro y conciso.
Fantastic video!
Thank you Max!
Thanks Ryan! works like a charm!
Awesome, my pleasure!
Thank you, Ryan. Really appreciated.
🙏
It is my pleasure!
This was great - very useful content. Thanks for putting this out there!
Glad you enjoyed it! It's my pleasure David
Awesome video, just getting into stocks, and this is a great way for me to keep track!
Great to hear! I hope it helps you!
Awesome Video! Keep up the Great WorK
Thank you, I appreciate it and will do!
Great job man 🔥✨
Thanks for this! helps learning to use Google Sheets
Glad you got some value out of it!
Helpful Video. Thanks.
Glad it was helpful!
Great info video.👍👍👍
Thank you.😊
My pleasure Alford!
Great video! Thanks!
Do you have any recommendation how to track each purchase of same stock in some timeline? Cause each purchase can have different price. Is it better to track this on different sheet?
My pleasure! You can follow the methodology that I used in this video to accomplish that: th-cam.com/video/6MlzMZ-B2ao/w-d-xo.html
it would be nice if the transaction sheet for multiple buys and sells of same stocks was also included, just like what you did for the excel equivalent
I just added your video idea to my list! For anyone curious, he is referring to this video which includes transactions and dividends in excel: th-cam.com/video/6MlzMZ-B2ao/w-d-xo.html
May I ask how I could include Buy and Sell multiple stocks in a transactions sheet in which I connect to this main Stock Dashboard? It would be grateful if you could make another video including “Buying&Selling” transaction sheet and Stock Portfolio Dashboards. Thanks!!!!
Hey Jason, I have a video of how to add a buy and sell transaction sheet into an Excel stock tracker here: th-cam.com/video/6MlzMZ-B2ao/w-d-xo.html
I can look into replicating this in Google Sheets in the future
Hi Ryan, Do you know how to get EV google sheet formula from yahoo finance statics table ?
Hi there! Yes, you can retrieve the Enterprise Value (EV) from Yahoo Finance by using the IMPORTXML function in Google Sheets, targeting the specific element on the Yahoo Finance statistics page where the EV is displayed. Just a warning, web scraping can get weird and messed up if Yahoo Finance makes changes to the page in the future
How do we make this work for a different currency - I have stocks in both USD and CAD?
how you do it, to put the price for stock, when you buy same stock couple times for different prices ? do you mean to use average price ?
Use a weighted average for the share prices in this situation. For example, if I bought 10 shares for $10, and 5 shares for $30, I would put $20 (= (($30*5)+($10*10))/(10 + 5))
@@RyanOConnellCFA thanks for advice and good video. Ive made my portfolio sheet today too finaly
Hi there I'm thinking about buying your template. Does it work with EM stocks, particularly Thailand?
Hello, thank you for considering purchasing the file! I believe that should work for you if you set the exchange to Stock Exchange of Thailand. You can always test that out in a basic excel sheet before purchasing the file using the =STOCKHISTORY() function
Should the wrapped section easily delete or did I do something wrong ?
Hey Matthew, could you point me to the timestamp you are referring to?
Is there a way to add Cash Flow through Google Finance?
Hey Guys is there a way for the google finance function to show me the currency of the stock?
Certainly! The Google Finance function in Google Sheets can provide the currency of a stock using the attribute "CURRENCY". For example, to get the currency for Apple Inc., you would use =GOOGLEFINANCE("NASDAQ:AAPL", "CURRENCY"). This will return the currency in which the stock is traded.
I have a question . umm, can I also use this same sheet for Mutual Funds instead?
What do you do if it says n/a for market cap, I put the ticker symbol but I don't think it is recognised
Hi, does the stock tracker update prices by itself?
Yes it will as long as you are connected to the internet!
How can I get the "Current price" for ZPRX and ZPRV small value cap etf ?
Hello, are you not able to pull those prices using the =GOOGLEFINANCE formula?
I don't know how to find how Google finance call these ETF. In website they are called ZPRX and ZPRV but it dosn't work with Google finance@@RyanOConnellCFA
Superb, Dear can you help me out to have the Value of indian stock in Rupees.
ther is a fault in column "E" can not edited
hello Ryan, I paid for the sheet for the tracking sheet, can you please send me the file. i am new to google sheets and i may have lost it. thx much
I have just sent it your way. It is possible that it ended up in your spam folder
How would I add a DRIP column?
What does DRIP stand for?
@@RyanOConnellCFA dividend reinvestment program
Can I create a tracker for example Nigerian stocks? I can see all the companies are US companies.
To my knowledge, Google Sheets does not natively support Nigerian stocks unfortunately
@@RyanOConnellCFA thanks I figured also.
@@Chuksenyi Wish I could have been of more help!
Hi. Could you please explain how to track a trailing stock loss?
Hello Edidson, do you mean a trailing stop loss?
Hello Ryan, thanks for asking.
yes that’s what I mean.
Unfortunately, this dashboard isnt complex enough to add a trailing stop loss. I will likely revisit this concept in the future and make a more sophisticated dashboard however
Hi there, Could you send me the file pls. I have paid but am unable to find the file or the link doesn’t work? Thank you.
Hello, thank you for purchasing the file! Please email me at ryan@ryanoconnellfinance.com with your order number and I will send you the file
how to replace the figures in column "E"? thanks
What do you do if it says n/a for market cap, I put the ticker symbol but I don't think it is recognised