How To Make A Stock Watchlist In Google Sheets

Подписаться 35 тыс.

Просмотров 121 тыс.

50% 1

How To Make A Stock Watchlist In Google Sheets
📈 Link to download The Stock Watch List Tracker: / thinkstocks
🌎 Follow me on Twitter: / keithfrislid
🎥 Here is the camera I use for my videos: amzn.to/2PB865Q
🔶Get 50% off Seeking Alpha Premium with my Promo Link!!!🔶
www.sahg6dtr.com/28KL61/R74QP/
In this video, I will walk you through how to make a stock watchlist in google sheets. This video will not only teach you now to pull market data like price, market cap, and company names, but also how to make buy and sell signals and even pull an annual stock price chart for each stock on your watchlist! All the functions are below but I encourage you to watch the video from start to finish so you know how all the functions work and to help out my RU-vid algo! Make sure to smash that like button, subscribe and share this video as well!
🔷Google Sheets Functions:
🔸Price: =IF(ISBLANK(A13),"",GOOGLEFINANCE(A13,"Price"))
🔸Change: =IF(ISBLANK(A13),"",GOOGLEFINANCE(A13,"changepct")/100)
🔸52 Week Low: =IF(ISBLANK(A13),"",GOOGLEFINANCE(A13,"low52"))
🔸52 Week High: =IF(ISBLANK(A13),"",GOOGLEFINANCE(A13,"high52"))
🔸 Target Buy: IF(ISBLANK(A13),"",((F13-E13)*0.5)+E13)
🔸 Target Sell: =IF(ISBLANK(A13),"",((F13-E13)*0.85)+E13)
🔸Trend Line:=SPARKLINE(GOOGLEFINANCE(B3,"price",today()-364,today()))
RU-vid Doesn't Allow greater than or less than signs in the description so I replaced them with these symbols: "▶","◀"
🔸 Signal: =IF(ISBLANK(A13),"",IF(C13▶H13,"Sell",if(C13◀G13,"Buy","Hold")))
🔸 Single Stock Signal: =IF(B4▶((B6-B7)*0.85+B7),"Sell",if(B4◀((B6-B7)*0.5+B7),"Buy","Hold"))
AFFILIATE DISCLOSURE: Some of the links on this channel and in video descriptions are affiliate links. At no additional cost to you, we receive a commission if a purchase is made after clicking the link.
#watchlist #googlesheets #stockspreadsheet

Опубликовано:

17 дек 2020

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 159

@miroszn 3 года назад

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.

@gagnepaingilly 4 месяца назад

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 4 месяца назад

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 4 месяца назад

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 4 месяца назад

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 4 месяца назад

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

@jeremiahburkett4172 3 года назад

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

@TecNificant 3 года назад

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

@ThinkStocks 3 года назад

Will do!

@alexh.7929 3 года назад

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

@padraigmarley2844 Год назад

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

@David-jv7ot 3 года назад

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 3 года назад

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

@David-jv7ot 3 года назад

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

@zenitzuboy8902 3 года назад

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

@supernico2879 3 года назад

Thanks you so much, gorgeous job ! very helpfull

@PriankaEvangelineSLP 3 года назад

So useful! Liked and subscribed. ❤️ Thank u

@ImVengeanceee 3 года назад

Great content bro, thanks!!

@willhatt67 3 года назад

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.

@pablogo3404 2 года назад

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

@VIJAIKUMARRGH 3 года назад

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

@stephensmith9504 11 месяцев назад

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

@hmubarakail 6 месяцев назад

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

@jr4487 2 года назад

excellent content, subscribed and liked it!

@rameshkumar3378 2 года назад

BEST CHANNEL FOR LEARNING

@vdogra1 3 года назад

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?

@billcliinton1005 3 года назад

Big help! Thanks!

@stepheneuloth3826 3 года назад

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 3 года назад

This is great info! Thanks Stephen!

@sateshraj1742 3 года назад

Great work 👍🏻🙏🏻

@ykam9313 3 года назад

Thanks Man!!!! your video hits different

@jeremiahburkett4172 3 года назад

Don’t it though.

@Nigromancy 3 года назад

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

@ThinkStocks 3 года назад

Thanks for smashing it!

@ngo5614 3 года назад

awesome, thank you

@DrunknOdin 2 года назад

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.

@Vallyy77 Год назад

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

@HelloBhujpur 2 года назад

Excellent Video. Thank You so much.. .

@rohinisharma3340 5 месяцев назад

Awesome bro

@rohitpriyadarshi6220 2 года назад

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

@michaelstevens3549 3 года назад

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

@ThinkStocks 3 года назад

Thanks!

@mahfouz8397 3 года назад

That is wonderful, thank you very much

@ThinkStocks 3 года назад

You're welcome!

@cpprulez 3 года назад

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 3 года назад

Very interesting! Thanks for that!

@cpprulez 3 года назад

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

@ThinkStocks 3 года назад

Glad you find them useful!

@mjs28s 3 года назад

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 3 года назад

I'll have to start incorporating VLOOKUP!

@user-of4dq4ij8g 2 года назад

Love this

@July-ct7pp 3 года назад

"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 3 года назад

Lmao😂😂😂 thanks!

@user-dt1lt1wn7o 4 месяца назад

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

@mfatihd88 4 месяца назад

thank you

@mickommers 3 года назад

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

@rarunkumar1000 Год назад

Great video🤩🤩

@easylearningwithRP 3 года назад

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

@SuperKevinFS 3 года назад

EXCELLENT VIDEO

@lasanthadilhan8281 3 года назад

Thanks 🙏 I made it same

@Saad_TheGod 2 года назад

Great Vid!

@arielsigler948 2 года назад

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

@loustj7800 3 года назад

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

@DLCShark 3 года назад

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?

@edwardbrandt7566 3 года назад

Cool stuff!:)

@urayys 3 года назад

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

@javierloa9197 2 года назад

Stack market! Love it! hahaha :)

@maxiperelli8570 3 года назад

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

@Matta-bv5ms 4 месяца назад

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

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

@murators4732 11 месяцев назад

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

@terry2346 3 года назад

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.

@sadashivb5100 3 года назад

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

@user-lp9cc3yk7o 3 месяца назад

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

@iansnow2830 2 года назад

How do you view stocks from different markets?

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

@TheRobFather 3 года назад

Thank you very much, your freaking awesome.

@ThinkStocks 3 года назад

Thanks man!

@TheRobFather 3 года назад

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

@ThinkStocks 3 года назад

Lol, thanks for helping boost my RU-vid algorithm!

@PlanBMarketing Год назад

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

@OLGMC 3 года назад

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 3 года назад

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

@OLGMC 3 года назад

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

@puransingh6999 Год назад

UR GREAT PERSONALTY

@sahithbasani4475 Год назад

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

@abhi0fight 3 года назад

I want to like this video 10 times.

@hemilcivil3909 5 месяцев назад

Nice sir

@ateeqbashiruddin1479 3 года назад

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

@iceman5413 3 года назад

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.

@amitc3coral1 5 месяцев назад

Superb

@edwinani5259 2 года назад

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.

@bullpat1479 3 года назад

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

@ThinkStocks 3 года назад

Glad you like it and it works for you!

@eskay1959 4 месяца назад

Awesome presentation. A great learning. Thanks for sharing.

@safvansaiyad9527 Год назад

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

@vishalkamath8327 2 года назад

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

@robira1313 3 года назад

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

@mohammadfarooque2116 Год назад

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

@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

@recantodogalo9488 Год назад

SHOW DE BOLA!

@keeganwidmyer5822 3 года назад

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

@ThinkStocks 3 года назад

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 3 года назад

@@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 3 года назад

That tutorial is coming out this Friday!

@marysmith3710 Год назад

Can you add ex dividend date & dividend payment date?

@manojmakwana965 Год назад

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

@janostasi8236 3 года назад

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

@jsirin755 5 месяцев назад

Its target of buy and Target of sell really work?

@pasupathy1314 5 месяцев назад

Can i make a watch list for indian stock market sir?

@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

@metano2280 Год назад

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

@augustin9999 3 года назад

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

@manfredadams3252 3 года назад

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.

@pedrocunha6680 3 года назад

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

@miroszn 3 года назад

You've made my day. Thanks

@spazier16 Год назад

I LOVE YOU

@mustainjiddan6345 3 года назад

why error for formula ?

@jasonr3165 3 года назад

How comes this channel only has 9.45k subscribers.

@ThinkStocks 3 года назад

Honestly my favorite kind of comment! Slow and steady growth

@kimekberg7434 2 года назад

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 2 года назад

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"}

@jefffox8991 Год назад

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

@RetroModeDays 2 года назад

My sparkline is not working

@RoadRunner-Dollars 3 года назад

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

@smudgepost Месяц назад

Please share template

@rsiexpress 2 года назад

gREAT wORK

@uttammore366 3 года назад

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

@ThinkStocks 3 года назад

Yes it should work for any international stock!

@uttammore366 3 года назад

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

@aaronalphonso9229 3 года назад

You could have just used lookup formulas for the widget part

@hannahacoaching Год назад

Column B says Singal, what is that?

@lewinmg 15 дней назад

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

@akashmadhusudhanan8781 3 года назад

can anybody share their google sheets they created like this?

@ThinkStocks 3 года назад

All my good sheets including this one are on my Patreon, the link is in the description!

@kevinkweya 2 месяца назад

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

@Toohottorot 3 года назад

fixed the issue with the market cap buy just adding an if statement that says if it's less that 1B, then do the formula for millions. =if(GOOGLEFINANCE(B3,"marketcap")>1000000000,"$"&Trunc(GOOGLEFINANCE(B3,"marketcap")/1000000000)&"B","$"&Trunc(GOOGLEFINANCE(B3,"marketcap")/1000000)&"M")

@ThinkStocks 3 года назад

Amazing! Thanks for sharing this!