This works for ETFs really well. I grabbed a list of all the current US-based ETFs with their full name and asset class. Moved columns two spaces over to make room then dumped the entire list into the sheet. To make it even cooler, I copied the sparkline command onto the next available cell to the right, modified the cell reference, then copied that to every cell below it in that column, so now all those rows have a 12 month trend line graph.
English is not my mother's language, and I usually skip tutorials where authors try to be quick or don't care about pronunciation, yet the Internet has an international audience. You are a gifted educator. I learned a lot here. Thank you and good luck.
I found a ton of value in your videos and sincerely thanks! I wanted to give back a bit by adding some formulas to support the Market Cap Formulas for T, B, M: 1- in outside cell=if(isblank($B$3),"",GOOGLEFINANCE($B$3,"marketcap")) 2- Count the length of characters =LEN(L7) 3- Add a T, B or M =if(AND(K8=20),"T",if(AND(K8=16),"B",if(AND(K8=13),"M"))) 4- Round Up the Market Cap =ROUNDUP(if(AND(K8=20),L7/1000000000000,if(AND(K8=16),L7/1000000000,if(AND(K8=13),L7/1000000)))) 5- Concat in the dashboard =CONCAT("$",CONCAT(M8,L8)) Might have missed something but I hope this helps - thanks again Think Stocks! - I am Thinking Stocks!!!
While this provide an actual explanation of the market, on how you buy your stocks. After buying stocks for just over 10 years, i'm still struggling to make gains. How do i adjust or revamp my $2M portfolio? should i consider some defensive investments?
In this current unstable markets, It is advisable to diversify while retaining 70-80% in secure investments. looking at the worth of your portfolio, you should consider financial advisory.
Agreed, my portfolio is well-matched for every market season yielding 85% from early last year to date. I and my advisr are working on a 7 figure ballpark goal, tho this could take another year. IMO, financial advisors are the most sought-after professionals after doctors.
She goes by ‘’Jennifer Leigh Hickman’ I suggest you look her up. To be honest, I almost didn't buy the idea of letting someone handle growing my finance, but so glad I did.
Thank you for sharing, I must say, Jennifer appears to be quite knowledgeable. After coming across her web page, I went through her resume and it was quite impressive. I reached out and scheduled a call
I dont know if you still read these messages, Im a little confused by the signal on the top section and its formatting, as I entered the format correctly and it correctly gave the info, however the signal from the bottom section had an equation using your target buy and target sell, this gave results whether to hold sell or buy, when we use the format for the top section, we only use the 52 week high and low, for me this makes coca colas stock state sell on the bottom, but hold and the top! Any reason this would happen?
I have been trying to find a script for googlefinance that would give me the 52 week high and 52 week low on a particular date. I have been looking at this sheet and noticed that you did a graph for a stock from it's past and I noticed that when you don't add the sparkline it shows the actual prices for that day but I was looking for a way to find the 52 week high and low so tried replacing the price to 52low and 52 high but that didn't work. Is there a way to get it for a past date?
My Friend you have just improved so much for me and my trading, liked and subscribed is all i can do to say thank you so much for a brilliantly put together tutorial, and so well explained, congratulations
@@ThinkStocks Fantastic, i have been trying to figure away to pull the Average True Range from GoogleFinance but that seems pretty impossible but " hey ho" thank you once again
Too good and your explanation is excellent. My question is whether we can show the price line [along with sparkline] as we do the the graph. Looking forward for more videos... Regards
How do I create a list of stocks that updates as the list changes. For example, S&P500, Top Gainers, Top Losers, Stock Splits, etc? Also, how do I get analyst prediction prices, stock split details ( dates, split ratios), earnings announcement dates?
Great content thanks, helps a lot. Wonder if you can point me in the right direction with a formula I’m struggling with. I have 2 columns that I want to show, in a third column the percentage of the two columns, at the moment I have =(A-B)/B. This works if A is larger than B showing the correct percentage but if B is larger than A it shows the wrong percent. Basically I’m trying to show a + or - percent between the two columns. Hope you can help.
Another way you can format the Market Cap instead of using the long formula is to go into more formats > custom number formats > then paste this in the field ($#,##0,,," B") DO NOT ADD THE BRACKETS. that should change it to billions and just deduct the comma's to get to the millions
Think you could make a video on how you are able to determine which chart/table you are pulling in from. For example on FINVIZ you're able to scrape data from table 8 but how did you know that is table 8? Thanks a bunch love you videos.
Great tutorial in making a portfolio tracker!!! Having an issue with the 'Purchase' price calculation, when we have multiple transactions of buy and sell. The purchase price comes off. Is there any other way to calculate it?
the vedio was awesome to make our watch list thankyou somuch my query upon the video that make the macd and rsi indicator signal implement on the google sheet is very useful to trade knwoledge
Hi Thank you so much. How can I create a graph view of my total portfolio value over few days, weeks and so on. Actually a trend line of my portfolio. Thank you
Really enjoy these videos. Small suggestion: instead of duplicating the single stock signal, it can be taken directly from the watchlist. I think something like =INDEX(A13:H50, MATCH(B3,A13:A50), 2) will do.
VLOOKUP works too for any data that you are repeating, just look it up from the main dataset.. I use that often. Seems like when I have too many cells going out to the internet for the same data that my spreadsheet doesn't update properly so I started grabbing data one time and then just copying it around within the sheet (or tabs) as needed. Took awhile to get there but once I got my spreadsheet wired down to what I wanted it was pretty easy to clean up and just query Google Finance one time, then all data across multiple tabs just grabs the data from within the spreadsheet. Love google sheets!
When using googlefinance you use the cell reference not the ticker value directly. This is very practical as you show however can you easily sort such a list to have the shares in alphabetical order ? When I tried this previously it seems to loose the references
Pulling data from finviz and removing the * from the imported data, google sheets only recognizing information as text and can't get it as data. Any idea on how to covert to data? great vids thanks
@@ThinkStocks Ok, awesome! Have you uploaded anything about a Cryptocurrency tracker for Google Sheets yet? This is because I can't figure out how to use the ticker to import data for crypto in Google Sheets.
It's a good video. One thing I do not understand, can this sheet communicates with your crypto provider? So if you put a coin on sale, is this happening real time. or do you have to do this separate? thanks in advance for the answer
"If you haven't hit that like button that's just disrespectful" LMAO I was so into the video I forgot to hit that smash like button thanks for the reminder lol I hit that button so hard
Yes. Depending on which market the stock is trading on, just use "tse:ticker", "cnsx:ticker" or "cvs:ticker". Those are the ones I'm familiar with. So to sum it up, type in the exchange, then a colon, then the ticker symbol. No spaces, no quotations.
I have a column (H) and a column ( I). I want to conditionally format H18 so that if the number in H18 is greater than the number in I18 the color of the cell will change to dark green. If h18 is less than i18 the cell would be red. Could you give me a formula that I could use to accomplish that? Thank you
I dont know if you already did one but a portfolio for people that have stocks with different currencies would be a big help! You already helped out a lot but some handy functions these portfolios have require all the numbers to be the same currency :/
Google finance deals automatically with NYSE, NASDAQ and TSE (Toronto STocks). What about other exchanges such as TSX-V and NEO exchange, do you know how I can plug them into the tracker? Thank you by the way for your videos. I'm a beginner and I learn a lot.
I had to slow you down a bit as my keyboarding skills aren't the greatest but it turned out great! Thanks so much! Now just need to add in the initial cost and number of shares and good to go.
Is there any way to build a formula that will build off of goggle's chart gain/loss in %/$ that shows above their chart? I would like to see the average +/- of a stock for any year that I enter, not just the 5yr and 10yr option they have available? I don't need the chart, I would just like the % and $ amount for any time period that I enter
WTB: Intuitive display of where current value falls within 52w h/l or 5 year yield/5 year price/book using a single cell instead of 3. Normalize the value and draw 3 symbols perhaps? Cheat with ascii art? L---*--------H Not sure what the limitations are but such a widget would sure be an watchlist upgrade.
This works for ETFs really well. I grabbed a list of all the current US-based ETFs with their full name and asset class. Moved columns two spaces over to make room then dumped the entire list into the sheet. To make it even cooler, I copied the sparkline command onto the next available cell to the right, modified the cell reference, then copied that to every cell below it in that column, so now all those rows have a 12 month trend line graph.
English is not my mother's language, and I usually skip tutorials where authors try to be quick or don't care about pronunciation, yet the Internet has an international audience. You are a gifted educator. I learned a lot here. Thank you and good luck.
When they say resources, you are one of em. Ty
I found a ton of value in your videos and sincerely thanks! I wanted to give back a bit by adding some formulas to support the Market Cap Formulas for T, B, M:
1- in outside cell=if(isblank($B$3),"",GOOGLEFINANCE($B$3,"marketcap"))
2- Count the length of characters =LEN(L7)
3- Add a T, B or M =if(AND(K8=20),"T",if(AND(K8=16),"B",if(AND(K8=13),"M")))
4- Round Up the Market Cap =ROUNDUP(if(AND(K8=20),L7/1000000000000,if(AND(K8=16),L7/1000000000,if(AND(K8=13),L7/1000000))))
5- Concat in the dashboard =CONCAT("$",CONCAT(M8,L8))
Might have missed something but I hope this helps - thanks again Think Stocks! - I am Thinking Stocks!!!
This is great info! Thanks Stephen!
This is realy awsome,👌👌 can we draw trendlines on the google sheets, or can we add up in this chart itself? your guidance awaited.👌
So useful! Liked and subscribed. ❤️ Thank u
Love it. Did not know how great Google Sheets are until I started watching your channel...thanks
Another great tutorial. I added this as a sheet to the portfolio tracker. Keep em coming....:-)
Will do!
While this provide an actual explanation of the market, on how you buy your stocks. After buying stocks for just over 10 years, i'm still struggling to make gains. How do i adjust or revamp my $2M portfolio? should i consider some defensive investments?
In this current unstable markets, It is advisable to diversify while retaining 70-80% in secure investments. looking at the worth of your portfolio, you should consider financial advisory.
Agreed, my portfolio is well-matched for every market season yielding 85% from early last year to date. I and my advisr are working on a 7 figure ballpark goal, tho this could take another year. IMO, financial advisors are the most sought-after professionals after doctors.
She goes by ‘’Jennifer Leigh Hickman’ I suggest you look her up. To be honest, I almost didn't buy the idea of letting someone handle growing my finance, but so glad I did.
Thank you for sharing, I must say, Jennifer appears to be quite knowledgeable. After coming across her web page, I went through her resume and it was quite impressive. I reached out and scheduled a call
I dont know if you still read these messages, Im a little confused by the signal on the top section and its formatting, as I entered the format correctly and it correctly gave the info, however the signal from the bottom section had an equation using your target buy and target sell, this gave results whether to hold sell or buy, when we use the format for the top section, we only use the 52 week high and low, for me this makes coca colas stock state sell on the bottom, but hold and the top! Any reason this would happen?
BEST CHANNEL FOR LEARNING
I have been trying to find a script for googlefinance that would give me the 52 week high and 52 week low on a particular date. I have been looking at this sheet and noticed that you did a graph for a stock from it's past and I noticed that when you don't add the sparkline it shows the actual prices for that day but I was looking for a way to find the 52 week high and low so tried replacing the price to 52low and 52 high but that didn't work. Is there a way to get it for a past date?
The most great watchlist ever seen
Just finished doing it. Great tool to help me look at the trends. Thanks very much.
THANK YOU SIR , I CREATED SAME GOOGLE SHEET VIA YOUR WORTHFULL VIDEO , THANK YOU SO MUCH FOR YOUR VALUABLE VIDEO.
My Friend you have just improved so much for me and my trading, liked and subscribed is all i can do to say thank you so much for a brilliantly put together tutorial, and so well explained, congratulations
I'm glad you enjoyed it! I got plenty more to come!
@@ThinkStocks Fantastic, i have been trying to figure away to pull the Average True Range from GoogleFinance but that seems pretty impossible but " hey ho" thank you once again
Great videos mate, very very helpful for a noob like me, appreciate
Thanks for this. I used your formula and put in the 6 stocks I own, they're all buys. YAY 😀
Too good and your explanation is excellent. My question is whether we can show the price line [along with sparkline] as we do the the graph. Looking forward for more videos... Regards
sir can u make a vedio ema cross over signal in live market for stock ..........multi time frame
Awesome bro
Amazing work with Excel (google sheet).👏👏👏👏
hello sir i like this vedio and also could u please tell us how to creat our own ones ?
How do I create a list of stocks that updates as the list changes. For example, S&P500, Top Gainers, Top Losers, Stock Splits, etc? Also, how do I get analyst prediction prices, stock split details ( dates, split ratios), earnings announcement dates?
Great content thanks, helps a lot. Wonder if you can point me in the right direction with a formula I’m struggling with. I have 2 columns that I want to show, in a third column the percentage of the two columns, at the moment I have =(A-B)/B. This works if A is larger than B showing the correct percentage but if B is larger than A it shows the wrong percent. Basically I’m trying to show a + or - percent between the two columns. Hope you can help.
excellent content, subscribed and liked it!
Another way you can format the Market Cap instead of using the long formula is to go into more formats > custom number formats > then paste this in the field ($#,##0,,," B") DO NOT ADD THE BRACKETS. that should change it to billions and just deduct the comma's to get to the millions
Think you could make a video on how you are able to determine which chart/table you are pulling in from. For example on FINVIZ you're able to scrape data from table 8 but how did you know that is table 8? Thanks a bunch love you videos.
Great tutorial in making a portfolio tracker!!! Having an issue with the 'Purchase' price calculation, when we have multiple transactions of buy and sell. The purchase price comes off. Is there any other way to calculate it?
the vedio was awesome to make our watch list thankyou somuch my query upon the video that make the macd and rsi indicator signal implement on the google sheet is very useful to trade knwoledge
Can you make a watch list of stocks that use the MACD indicator as a buy sell alert?
Yoooo! Bad ass! Just subbed and following you like a Hawk! Thanks for this!!!!
Can you add ex dividend date & dividend payment date?
Hi Thank you so much. How can I create a graph view of my total portfolio value over few days, weeks and so on. Actually a trend line of my portfolio. Thank you
Amazing!! How can we also implement indicator values? Like macd and a signal for macd cross❤❤❤
Really enjoy these videos. Small suggestion: instead of duplicating the single stock signal, it can be taken directly from the watchlist. I think something like =INDEX(A13:H50, MATCH(B3,A13:A50), 2) will do.
Very interesting! Thanks for that!
Welcome :) I'm more of a Mac Numbers user, but I find your videos on google spreadsheets very helpful, thanks!
Glad you find them useful!
VLOOKUP works too for any data that you are repeating, just look it up from the main dataset.. I use that often.
Seems like when I have too many cells going out to the internet for the same data that my spreadsheet doesn't update properly so I started grabbing data one time and then just copying it around within the sheet (or tabs) as needed. Took awhile to get there but once I got my spreadsheet wired down to what I wanted it was pretty easy to clean up and just query Google Finance one time, then all data across multiple tabs just grabs the data from within the spreadsheet.
Love google sheets!
I'll have to start incorporating VLOOKUP!
Great work 👍🏻🙏🏻
Great video🤩🤩
I followed your instructions on how to make a portfolio. Then someone else’s for a watch list.
Guess who’s about to redo the whole Google sheet?
Can you make another video in moving Average by customised with working day only
Great video. Similar to what you would do in Excel but easier.
Its target of buy and Target of sell really work?
When using googlefinance you use the cell reference not the ticker value directly. This is very practical as you show however can you easily sort such a list to have the shares in alphabetical order ? When I tried this previously it seems to loose the references
Maybe try locking the cell references. If they are locked, try modifying the lock
How do you view stocks from different markets?
Big help! Thanks!
Pulling data from finviz and removing the * from the imported data, google sheets only recognizing information as text and can't get it as data. Any idea on how to covert to data? great vids thanks
What time frame is the buy and sell weekly or monthly ?
Excellent Video. Thank You so much.. .
Great video! Could you make a dividend tracker video if you don’t have one up already?
I do have a couple trackers that tracks dividends but I am coming out with a video dedicated to dividend stocks and tracking your dividend income!
@@ThinkStocks Ok, awesome! Have you uploaded anything about a Cryptocurrency tracker for Google Sheets yet? This is because I can't figure out how to use the ticker to import data for crypto in Google Sheets.
That tutorial is coming out this Friday!
It's a good video. One thing I do not understand, can this sheet communicates with your crypto provider? So if you put a coin on sale, is this happening real time.
or do you have to do this separate? thanks in advance for the answer
"If you haven't hit that like button that's just disrespectful" LMAO I was so into the video I forgot to hit that smash like button thanks for the reminder lol I hit that button so hard
Lmao😂😂😂 thanks!
Thanks Man!!!! your video hits different
Don’t it though.
Hi,
Thanks for doing this. Is there any way to add Canadian stocks on to this watchlist?
Yes. Depending on which market the stock is trading on, just use "tse:ticker", "cnsx:ticker" or "cvs:ticker". Those are the ones I'm familiar with.
So to sum it up, type in the exchange, then a colon, then the ticker symbol. No spaces, no quotations.
I have a column (H) and a column ( I). I want to conditionally format H18 so that if the number in H18 is greater than the number in I18 the color of the cell will change to dark green. If h18 is less than i18 the cell would be red. Could you give me a formula that I could use to accomplish that?
Thank you
Hello Sir,
What to do with Indian Market i.e Nse..??
How to pull data from finviz for indian market portfolio?
Please explain..
Tc
Stack market! Love it! hahaha :)
I dont know if you already did one but a portfolio for people that have stocks with different currencies would be a big help! You already helped out a lot but some handy functions these portfolios have require all the numbers to be the same currency :/
I did one! International portfolio tracker. Posted it a few weeks ago
@@ThinkStocks awesome, I'll go check it out!
Google finance deals automatically with NYSE, NASDAQ and TSE (Toronto STocks). What about other exchanges such as TSX-V and NEO exchange, do you know how I can plug them into the tracker? Thank you by the way for your videos. I'm a beginner and I learn a lot.
Thanks for the content. Really liked this ....
Great content bro, thanks!!
HAHA Thanks for reminding me to smash the like button! Your Awesome!
Thanks for smashing it!
I am get formula parse error when I enter a target buy formula what to do?
Great Vid!
Great video. Thank you for your time to post this video
I want to like this video 10 times.
if someone have problems with the functions, just change "," to ";" !!!!
You've made my day. Thanks
I LOVE YOU
In my stock tracker, can I have a cell display YESTERDAY'S end value?
Quick question like a year later but, is there a way to color the sparkline? Have tried some ways but hasn't got it to work. Ty :)
Yes! Just change the text color and it will change the sparkline color
It can be changed in the formula by adding {"color", "green} or {"charttype", "column"}
thank you
I had to slow you down a bit as my keyboarding skills aren't the greatest but it turned out great! Thanks so much! Now just need to add in the initial cost and number of shares and good to go.
Is there any way to build a formula that will build off of goggle's chart gain/loss in %/$ that shows above their chart? I would like to see the average +/- of a stock for any year that I enter, not just the 5yr and 10yr option they have available? I don't need the chart, I would just like the % and $ amount for any time period that I enter
Great work
I apply this Nifty it's work
Thanks sir
With love from India
Glad you like it and it works for you!
Awesome presentation. A great learning. Thanks for sharing.
For market cap, instead of dividing by 1 billion, set custom number format to $#,##0,,," B"
Thanks you so much, gorgeous job ! very helpfull
Thanks 🙏 I made it same
Love this
How do I correct this formula to signal error appears =IF(B4>((B6-B7)*0.85+B7);"SELL";IF(B4
Thanks for Nice and Informative video, just suggest to use Custom Format for Market Cap -> #,##0,,,"B" instead of putting formula.
Great video...keep up the good work. And yes, I hit the "Like Button". LOL
Thanks!
Is this setup working in NSE from India please reply 🙏🙏🙏
Yes it should work for any international stock!
Yes I have make it's in Indian style hope it will work, I have spent too much time this experiment 🙏🙏
My sparkline is not working
Please share template
Are you using a PC
EXCELLENT VIDEO
UR GREAT PERSONALTY
Column B says Singal, what is that?
hahah...funny. i thought the same thing, its supposed to be "Signal". He misspelled it
#,##0,,, " B" Format=Number=Customer Number format, should easily fix the Market Cap thingy. Great video by the way.
Anyone willing to help me please why I failed? I typed excatly the same signal but for what reason for no result.
awesome, thank you
Nice sir
Great video...pls make how to track Roc Rock debit /equity Ratio
How to find the value of a stock on two dates?
That is wonderful, thank you very much
You're welcome!
why error for formula ?
WTB: Intuitive display of where current value falls within 52w h/l or 5 year yield/5 year price/book using a single cell instead of 3. Normalize the value and draw 3 symbols perhaps? Cheat with ascii art? L---*--------H Not sure what the limitations are but such a widget would sure be an watchlist upgrade.
How comes this channel only has 9.45k subscribers.
Honestly my favorite kind of comment! Slow and steady growth
google finance is broken they did an update? i cant fetch data for these anymore :(
Superb
Thank you very much, your freaking awesome.
Thanks man!
@@ThinkStocks Ive been watching ur vids since 1 am lol.
Lol, thanks for helping boost my RUclips algorithm!
Cool stuff!:)