How To Make A Stock Watchlist In Google Sheets

[@rdbrown58]

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.

[@miroszn]

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.

[@stepheneuloth3826]

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!!!

[@ThinkStocks]

This is great info! Thanks Stephen!

[@jeremiahburkett4172]

When they say resources, you are one of em. Ty

[@TecNificant]

Another great tutorial. I added this as a sheet to the portfolio tracker. Keep em coming....:-)

[@ThinkStocks]

Will do!

[@richardluna1996]

Love it. Did not know how great Google Sheets are until I started watching your channel...thanks

[@Matta-bv5ms]

This is realy awsome,👌👌 can we draw trendlines on the google sheets, or can we add up in this chart itself? your guidance awaited.👌

[@ezequielpalma10]

The most great watchlist ever seen

[@hmubarakail]

THANK YOU SIR , I CREATED SAME GOOGLE SHEET VIA YOUR WORTHFULL VIDEO , THANK YOU SO MUCH FOR YOUR VALUABLE VIDEO.

[@padraigmarley2844]

Thanks for this. I used your formula and put in the 6 stocks I own, they're all buys. YAY 😀

[@kalyanakumar-b9y]

Just finished doing it. Great tool to help me look at the trends. Thanks very much.

[@David-jv7ot]

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]

I'm glad you enjoyed it! I got plenty more to come!

[@David-jv7ot]

@@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

[@stephensmith9504]

Great videos mate, very very helpful for a noob like me, appreciate

[@PriankaEvangelineSLP]

So useful! Liked and subscribed. ❤️ Thank u

[@rameshkumar3378]

BEST CHANNEL FOR LEARNING

[@gagnepaingilly]

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?

[@sommersalt88]

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.

[@84gaynor]

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.

[@84gaynor]

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.

[@Curbalnk]

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

[@VENKATRAMANIYER-g7b]

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

[@Vallyy77]

Amazing work with Excel (google sheet).👏👏👏👏

[@VIJAIKUMARRGH]

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

[@wsround]

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?

[@pablogo3404]

Yoooo! Bad ass! Just subbed and following you like a Hawk! Thanks for this!!!!

[@alexh.7929]

Great video. Similar to what you would do in Excel but easier.

[@jr4487]

excellent content, subscribed and liked it!

[@DLCShark]

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?

[@cpprulez]

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.

[@ThinkStocks]

Very interesting! Thanks for that!

[@cpprulez]

Welcome :) I'm more of a Mac Numbers user, but I find your videos on google spreadsheets very helpful, thanks!

[@ThinkStocks]

Glad you find them useful!

[@mjs28s]

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!

[@ThinkStocks]

I'll have to start incorporating VLOOKUP!

[@djherro9238]

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?

[@DrunknOdin]

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.

[@Nigromancy]

HAHA Thanks for reminding me to smash the like button! Your Awesome!

[@ThinkStocks]

Thanks for smashing it!

[@yadelKarim]

Thanks Man!!!! your video hits different

[@jeremiahburkett4172]

Don’t it though.

[@murators4732]

Amazing!! How can we also implement indicator values? Like macd and a signal for macd cross❤❤❤

[@HelloBhujpur]

Excellent Video. Thank You so much.. .

[@willhatt67]

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.

[@bullpat1479]

Great work I apply this Nifty it's work Thanks sir With love from India

[@ThinkStocks]

Glad you like it and it works for you!

[@eskay1959]

Awesome presentation. A great learning. Thanks for sharing.

[@RideBnB_withus]

Awesome bro

[@keeganwidmyer5822]

Great video! Could you make a dividend tracker video if you don’t have one up already?

[@ThinkStocks]

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!

[@keeganwidmyer5822]

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

[@ThinkStocks]

That tutorial is coming out this Friday!

[@zenitzuboy8902]

Great video. Thank you for your time to post this video

[@terry2346]

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.

[@javierloa9197]

Stack market! Love it! hahaha :)

[@OLGMC]

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 :/

[@ThinkStocks]

I did one! International portfolio tracker. Posted it a few weeks ago

[@OLGMC]

@@ThinkStocks awesome, I'll go check it out!

[@billcliinton1005]

Big help! Thanks!

[@vdogra1]

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?

[@andyw.8402]

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?

[@michaelstevens3549]

Great video...keep up the good work. And yes, I hit the "Like Button". LOL

[@ThinkStocks]

Thanks!

[@PlanBMarketing]

Can you make a watch list of stocks that use the MACD indicator as a buy sell alert?

[@sateshraj1742]

Great work 👍🏻🙏🏻

[@supernico2879]

Thanks you so much, gorgeous job ! very helpfull

[@arielsigler948]

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

[@rarunkumar1000]

Great video🤩🤩

[@easylearningwithRP]

Thanks for Nice and Informative video, just suggest to use Custom Format for Market Cap -> #,##0,,,"B" instead of putting formula.

[@mohammadfarooque2116]

sir can u make a vedio ema cross over signal in live market for stock ..........multi time frame

[@rohitpriyadarshi6220]

Thanks for the content. Really liked this ....

[@manojmakwana965]

Can you make another video in moving Average by customised with working day only

[@ImVengeanceee]

Great content bro, thanks!!

[@Saad_TheGod]

Great Vid!

[@sahithbasani4475]

hello sir i like this vedio and also could u please tell us how to creat our own ones ?

[@urayys]

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

[@abhi0fight]

I want to like this video 10 times.

[@kevinkweya]

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

[@edwinani5259]

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.

[@etiennemolly7893]

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

[@jefffox8991]

Maybe try locking the cell references. If they are locked, try modifying the lock

[@mickommers]

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

[@vishalkamath8327]

Hello Sir, What to do with Indian Market i.e Nse..?? How to pull data from finviz for indian market portfolio? Please explain.. Tc

[@July-ct7pp]

"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

[@ThinkStocks]

Lmao😂😂😂 thanks!

[@loustj7800]

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

[@iansnow2830]

How do you view stocks from different markets?

[@ateeqbashiruddin1479]

Hi, Thanks for doing this. Is there any way to add Canadian stocks on to this watchlist?

[@iceman5413]

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.

[@marysmith3710]

Can you add ex dividend date & dividend payment date?

[@sadashivb5100]

Great video...pls make how to track Roc Rock debit /equity Ratio

[@mahfouz8397]

That is wonderful, thank you very much

[@ThinkStocks]

You're welcome!

[@jsirin755]

Its target of buy and Target of sell really work?

[@JDJRN]

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

[@metano2280]

How do I correct this formula to signal error appears =IF(B4>((B6-B7)*0.85+B7);"SELL";IF(B4

[@是熙]

Love this

[@robira1313]

In my stock tracker, can I have a cell display YESTERDAY'S end value?

[@maxiperelli8570]

What time frame is the buy and sell weekly or monthly ?

[@safvansaiyad9527]

I am get formula parse error when I enter a target buy formula what to do?

[@SuperKevinFS]

EXCELLENT VIDEO

[@jefffox8991]

For market cap, instead of dividing by 1 billion, set custom number format to $#,##0,,," B"

[@jasonr3165]

How comes this channel only has 9.45k subscribers.

[@ThinkStocks]

Honestly my favorite kind of comment! Slow and steady growth

[@kimekberg7434]

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

[@ThinkStocks]

Yes! Just change the text color and it will change the sparkline color

[@jefffox8991]

It can be changed in the formula by adding {"color", "green} or {"charttype", "column"}

[@BurhanAhmadi]

#,##0,,, " B" Format=Number=Customer Number format, should easily fix the Market Cap thingy. Great video by the way.

[@pedrocunha6680]

if someone have problems with the functions, just change "," to ";" !!!!

[@miroszn]

You've made my day. Thanks

[@spazier16]

I LOVE YOU

[@ngo5614]

awesome, thank you

[@mfatihd88]

thank you

[@lasanthadilhan8281]

Thanks 🙏 I made it same

[@puransingh6999]

UR GREAT PERSONALTY

[@janostasi8236]

Anyone willing to help me please why I failed? I typed excatly the same signal but for what reason for no result.

[@uttammore366]

Is this setup working in NSE from India please reply 🙏🙏🙏

[@ThinkStocks]

Yes it should work for any international stock!

[@uttammore366]

Yes I have make it's in Indian style hope it will work, I have spent too much time this experiment 🙏🙏

[@TheRobFather]

Thank you very much, your freaking awesome.

[@ThinkStocks]

Thanks man!

[@TheRobFather]

@@ThinkStocks Ive been watching ur vids since 1 am lol.

[@ThinkStocks]

Lol, thanks for helping boost my KZbin algorithm!

[@manfredadams3252]

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.

[@edwardbrandt7566]

Cool stuff!:)

[@RetroModeDays]

My sparkline is not working

[@hemilcivil3909]

Nice sir

[@augustin9999]

How to find the value of a stock on two dates?

[@smudgepost]

Please share template

[@ChannelAboutNothin]

Are you using a PC

[@observation-room]

Column B says Singal, what is that?

[@lewinmg]

hahah...funny. i thought the same thing, its supposed to be "Signal". He misspelled it

[@mustainjiddan6345]

why error for formula ?

[@RoadRunner-Dollars]

google finance is broken they did an update? i cant fetch data for these anymore :(

[@amitc3coral1]

Superb