New Excel STOCKHISTORY Function - Definitive Guide

[@WilsonC15]

I always enjoy your videos because you promote creative use cases and examples, as opposed to channels that only provide basic functions. I really like it!

[@MyOnlineTrainingHub]

I appreciate that! 🙏😊

[@mohammadj.shamim9342]

You blew my mind with this formula and your teaching method. Excellent work and Excellent teacher.

[@MyOnlineTrainingHub]

Wow, thank you, Mohammad!

[@HowToAnalyst]

Learned a lot and especially liked how the STOCKHISTORY function works with spilled ranges, thank you Mynda!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@udusegbe]

Definitely a “world first” & not just the new feature. Tons of investment analysis one can now do easily with this feature & the tips you showcased. Thanks & great video

[@MyOnlineTrainingHub]

My pleasure, Bare :-) Have fun with STOCKHISTORY when it becomes generally available.

[@GodGuy8]

Been using BatchGetSymbols but this looks promising it’s not set up to forecast in the current array but with some tweaking in vba it could be

[@hareshsivaraman4413]

Thanks a lot.. the last part is what I really needed. For others please do watch the full video the most needed is at the end. Appreciate ur hardwork. Pls continue

[@MyOnlineTrainingHub]

Glad it was helpful!

[@christinakral9530]

Can't wait to give it a try! Hopefully the function will be available soon! Thx again for a very valuable tutorial!

[@MyOnlineTrainingHub]

Great to hear you'll be able to make use of the STOCKHISTORY function, Christina!

[@ArnoldasEkleris]

Finaly!!! Thank you very much for this video! I was searching how to get portfolio value chart which is filling up automatically. This video made me shout Eureka!!! I guess I can choose the date I started buying stocks for my portfolio, enter stockhistory function to get prices for each stock which are in horizontal order. For dates which are in vertical order I can use sequence function as well. Like this I will get prices for each stock I own. Next step is to create a column to get share quantity at each price. I guess this should be entered manually. And then on a new column which is stock value, just need to multiply prieces each day with share quantity each day. I hope my interpretation of second part is right. Or is there any other way to get daily portfolio value chart? I tried to search it on the internet for so many days now and there is no info about it. And actually you are right! You are the first one to explain this method. Once again, thank you very much and hopefully will get an answer to my question on this comment board, or even in a new video explaining that. :*

[@MyOnlineTrainingHub]

So pleased you can make use of the STOCKHISTORY function :-) Yes, you will need to enter your stock holding manually and then you can calculate your stock value by multiplying the stock quantity by price.

[@venkatiyer7459]

Thanks a lot .... your teaching skill is too good ... though while watching the videos it seems very easy but when we try it's different. One should admit that you are teaching us a lot 🙏

[@MyOnlineTrainingHub]

Thank you! Glad I can help.

[@mimidolcelata5550]

Just came across this video while searching Stockhistory function. Brilliant examples and a lovely presentation. Well done. I subscribed immediately. Keep up the good work.

[@MyOnlineTrainingHub]

Many thanks, Mimi!

[@HM-wj4je]

Wow... This is amazing function. I am waiting for this function in my 365. Come on Microsoft...

[@MyOnlineTrainingHub]

It sure is! Hope it's generally available soon.

[@cards7651]

Always such a great teacher….❤

[@MyOnlineTrainingHub]

Thank you! Much appreciated 😊

[@PaulFinnigan]

Hi Mynda, thanks for a great walk through re StockHistory - excellent as always!

[@MyOnlineTrainingHub]

Cheers, Paul :-)

[@Tetracarbon]

Fantastic stuff. I learned quite a bit in this video and will use it to update my own apps.

[@MyOnlineTrainingHub]

Great to hear 😊

[@hougrel9514]

Marvellous, Wonderful - think of a superlative and you deserve it!

[@MyOnlineTrainingHub]

Thank you so much 😀

[@rajeshmanchikanti707]

Nice presentation and content, could you also guide us how to include close, open, high, low, volume based on date for list of stocks

[@MyOnlineTrainingHub]

Thank you! You can use the Stock data type to get the close, open, high, low and volume data.

[@monty2869]

Great information and explanation. Thank you.

[@MyOnlineTrainingHub]

Glad it was helpful, Monty!

[@solomonchekol3736]

i can't waiting your every tutorial. i love and you are super genious Keep it up!

[@MyOnlineTrainingHub]

Thank you so much!

[@silversolver7809]

Superb video Mynda, for what looks like a superb function. Thank you!

[@MyOnlineTrainingHub]

Glad you like it 😊

[@djambradollo4033]

You're doing a wonderful job!!

[@MyOnlineTrainingHub]

Thank you so much!

[@digitalkm]

Mynda, this is a great tutorial thank you very much. Also your website has lots of great content.

[@MyOnlineTrainingHub]

Glad you think so, Kevo!

[@patrickmadziar3860]

Very useful and well done. Thanks for sharing this with us.

[@MyOnlineTrainingHub]

Glad it was helpful, Patrick!

[@wayneedmondson1065]

Hi Mynda.. thanks for the 1080p version :))

[@MyOnlineTrainingHub]

You're welcome. I figured I might wait forever for YT to fix it.

[@darrylmorgan]

Hi Mynda!Really Looking Forward To Trying STOCKHISTORY Out...Thank You For The Great Tutorial :)

[@MyOnlineTrainingHub]

Cheers, Darryl! Hopefully it won't be too long before it's generally available.

[@katmai7777]

@@MyOnlineTrainingHub It won't be easy...but Could you please create Dashboard with "My Investment" in Standard Excel? I would like to see all my Stock, Bounds, Founds with date of Transaction (buy/sell). Gain/Loss for every transaction. Gain/loss for complete Wallet. Current wallet value... so Online scraping Data from Yahoo or Google Finance will be needed. Thanks in advance.

[@dominicomusi8753]

Exceptional content and presentation Mynda! Thank you and please continue to do what you do :)

[@MyOnlineTrainingHub]

Will do, Dominic 🙏

[@garikgharib973]

Hi Mynda, a great teaching. I have a question: What are the symbols for "NASDAQ", "DJIA", "S&P 500" that I can use in STOCKHISTORY function?

[@MyOnlineTrainingHub]

Hi Garik, from memory, you can't get overall exchange performance data with STOCKHISTORY. I think I managed to get one exchange, but not others and I can't remember which exchange worked.

[@chariepadilla5644]

I have used all your methods for my reports 😍

[@MyOnlineTrainingHub]

Wow, great to hear :-)

[@johnborg5419]

This is GREAT Myndy. I'm not much into stocks......but what is High and Low??

[@MyOnlineTrainingHub]

It's the highest price and lowest price the stocks sold for in that time period.

[@karstenfensmark3771]

Hi Mynda, Very fine video that help a lot. Did I miss a trick to get the P/E history data from Stocks in DATA TYPES or do you have a intro to this? Thanks in advance Karsten

[@MyOnlineTrainingHub]

Glad it'll be useful to you, Karsten! The P/E data isn't available, sorry.

[@karstenfensmark3771]

@@MyOnlineTrainingHub Thanks for reponding. Have a nice summer.

[@CaribouDataScience]

Thanks that was helpful. How can I hall all the stocks in one table with the stock symbol in the first row?

[@MyOnlineTrainingHub]

Great to hear! I'm not sure what you mean by your question. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@CaribouDataScience]

@@MyOnlineTrainingHub I posted the workbook to the forum.

[@davidjames3494]

Hi Mynda, very nicely done. I want to know if the StockHistory function can incorporate the array and sequence for multiple stock prices over the same periods. I don't want the date displayed for each stock but just a dynamic date in column, Stock Price Headers in Rows and prices for each below. Tx

[@annhirvell4926]

Thanks for this great tutorial, a great video!

[@MyOnlineTrainingHub]

Glad you liked it, Ann!

[@evolutionnotrevolution4475]

OMG!!! Simply magic!!! LOVE IT! Thank you so much. So relevant with all that's happening in the cryptocurrency space! Stay awesome o/

[@MyOnlineTrainingHub]

Thanks so much! :-)

[@chariscchi3140]

excellent video. Thank you again

[@MyOnlineTrainingHub]

Thanks so much!

[@LicoInvestments]

Awesome! Thank you. Is there a way to get just One day extracted, as opposed to showing the whole list of dates? , Thanks

[@MyOnlineTrainingHub]

Yes, you can just get data for a specific date/today's date.

[@pranlalmandalia729]

Thanks Sir I will try this again

[@MyOnlineTrainingHub]

Great to hear!

[@timboelsen6705]

Great video, as always. Is there a way to get historical dividend data? Thanks

[@MyOnlineTrainingHub]

Cheers, Tim! Dividend data isn’t available, unfortunately.

[@BestICan]

Sublime! Thank you, Mynda!

[@MyOnlineTrainingHub]

My pleasure, Randy!

[@ndamaze]

thank you so much for the video.

[@MyOnlineTrainingHub]

You're welcome!

[@davecope3322]

Thanks Mynda, great video Just need to wait for it - I'm not in the 50% :(

[@MyOnlineTrainingHub]

Cheers, Dave! This was the first time I've ever been in the right 50% :-D

[@celiahenderson5418]

Really useful tutorial, thank you.

[@MyOnlineTrainingHub]

Glad you enjoyed it, Celia!

[@aedwards9408]

Great video---i have one issue though---the dates will not refresh and give latest data for this new dates, even after refreshing all data----dates do no move on using =TODAY()

[@MyOnlineTrainingHub]

It may be that there isn't any data available for the current date for those stocks. You're welcome to post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

Hi Mynda, Happy New Year! As formula name signifies, I guess it can't provide real-time (or slightly delayed) data, right?

[@MyOnlineTrainingHub]

Happy new year to you too! Yes, STOCKHISTORY can only show the previous day's close. The Stock Data type can show the price as at 20 minutes ago.

[@parthraval6052]

FANTASTIC !!!! How much did you invest..lol

[@MyOnlineTrainingHub]

:-D cheers, Parth!

[@miepz7522]

Thanks for sharing, this is awesome. You’re awesome!

[@MyOnlineTrainingHub]

My pleasure. Glad you liked it 😊

[@sonakshah]

thank you for the video, very very informative, but i have an issue if you can help me, 1. can you help me get the data starting with the latest stock price first, 2. i tried getting data for say 10 stocks for 10 years but some of the stocks in the list are listed for less than 10 years than the price and date did not match is there a way to correct this, thanks in advance.

[@Gabriel2000779]

This is great! Just wanted to ask, is it possible to obtain % change in prices for the last month or 1 year for example. Entering a stock into any cell allows us to obtain information such as high, low, % change e.t.c. However, the % change applies to todays change. I would like to obtain the % change over the last month. Is this possible?

[@MyOnlineTrainingHub]

Glad you found it helpful, Gabriel! You can use STOCKHISTORY to get the price from a month ago and then separately also get today's price with STOCKHISTORY, which will allow you to calculate the percentage change from a month ago

[@aaronliss5869]

Great video! I'm trying to get data inputed for the last month, 3m, 6m, year. Is there a way to take out weekends and holidays from creating errors in the history (for stocks only traded on weekdays and non holidays?

[@MyOnlineTrainingHub]

Thanks, Aaron! You can handle errors with the IFERROR function.

[@raysurx2010]

I see that you are in Australia, how would I incorporate the Stockhistory from the NYSE? Or an American company that provides historical stock data like Yahoo? Thanks for the great videos.

[@MyOnlineTrainingHub]

You can use XNYS for NYSE as listed here on the STOCKHISTORY function source info: support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6

[@kerryp7014]

How can I get the % change for stock the last 5 trading days?

[@jerrydellasala7643]

So bummed. On Insider Beta and the function still isn't there. Thanks for sharing it though!

[@MyOnlineTrainingHub]

Yeah, unfortunately it’s a lucky dip. Hopefully it’ll be generally available soon.

[@RobertPuckett4]

What if I wanted to reference, say for example, the March high and low to compare months?

[@MyOnlineTrainingHub]

You could wrap STOCKHISTORY in the MAX and MIN functions to find the high and low of a date range.

[@m.raedallulu4166]

MS Excel is heading to the moon!

[@MyOnlineTrainingHub]

:-) Sure is!

[@georgetosounidis5545]

Game changer!

[@MyOnlineTrainingHub]

Sure is :-)

[@gregasmr8257]

Is there a way to get data for the stock indexes like SP500 or Dow Jones?

[@MyOnlineTrainingHub]

From memory only the NASDAQ is available.

[@jebise811]

Hi Mynda, I am trying to understand why monthly end dates are at the first of the new month? Is there a way to get the 31st or 30th closing price?

[@MyOnlineTrainingHub]

You'd have to specify the date if you want something other than the 1st of the month.

[@jebise811]

Thanks for your fast reply. I tried to specify the starting date with monthly intervals and it gives me always the first.

[@MyOnlineTrainingHub]

You can only specify the exact day for daily intervals. You need to make a list of the last day of each month as your input for the daily interval dates.

[@lastdance903]

Hi Mynda, great video as always! It seems that STOCKHISTORY function would show #N/A when there is a whole day trading halt on a certain date. Any ideas to skip the trading halt date or hide it as the #N/A value makes calculating MA a bit troublesome?

[@MyOnlineTrainingHub]

I can't replicate it, but have you tried using IFERROR?

[@lastdance903]

@@MyOnlineTrainingHub The stock I used was Afterpay. I did try IFERROR(, “ ”) function and it turns N/A to blank, but I was hoping to skip the entire row with N/A.

[@MyOnlineTrainingHub]

Ah, ok. It won't skip the row unless you give it the specific dates you want the data for as it's designed to return data for the date range you provide.

[@mikeniemotka9547]

Is there a way to get the S&P 500 index prices with this function?

[@MyOnlineTrainingHub]

You can get prices from any of these exchanges which are currently supported: support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6

[@Blackdocks]

Finaly!!! Unlick you tried to add the dates from sequence for stocks (not currencies) and dates were market are not open returns #value! any insight

[@Blackdocks]

And i find that you have the same issue in your file

[@MyOnlineTrainingHub]

Not sure what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@Blackdocks]

@@MyOnlineTrainingHub formula return #value! For saturday, sunday or closed day in your file whereas it's working in the video

[@TSSC]

Hi, I see that you have at least one unreleased rich data type, namely “Organization” (visible at 4:53). Can you please introduce this/these in a video? The recording shows: Organization, Stocks, Geography … and the expand button. This makes me curious. Have any other data types been added?

[@MyOnlineTrainingHub]

Well spotted :-) the organisation data types are new. They are connected to your own Power BI datasets and are only available to users with an E5 Microsoft 365 license, so they're not high on my list of topics due to the limited audience for them, but I'm sure I'll cover them at some stage. In the meantime, there is some info here on the Excel blog: techcommunity.microsoft.com/t5/excel-blog/simplifying-enterprise-data-discovery-and-analysis-in-microsoft/ba-p/1354160#

[@andreaagus7385]

Hi Mynda, i have noticed that the STOCKHISTORY function returns data one day delay, while in the Microsoft data documentation it says it should be only 20 min delay... do you know what is happening here? Thanks in advance!

[@MyOnlineTrainingHub]

STOCKHISTORY is always a day behind, hence 'history' in the name. Stock Data Types on the other hand, should only have a 20 minute delay.

[@andreaagus7385]

@@MyOnlineTrainingHub thank you !

[@antoniogarrido3058]

Hy Minda! I've just signed for Beta Channel but.. It doesn't work yet, it doesn't seem to recognize function arguments. Should I wait for some update or notifications... Thanks!!!!

[@MyOnlineTrainingHub]

Hi Antonio, you should have this function AFAIK. check your version number, mine is Version 2012 build 13430.20000

[@elhumam]

Lovely :) thanks.

[@MyOnlineTrainingHub]

My pleasure, Laeeq!

[@oscarhollywood]

Hi, thankyou for the video, but at 9:00 i copied that same code and it did not display 30 days of prices from current date, but only the current days price... Ive checked it probably 10 times now and don't know why it won't work

[@MyOnlineTrainingHub]

Not sure. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@jagsingh2673]

Hi there , There was certain formulation that i wanted to learn. It was about creating a master sheet and flowing that info into various sheets . I have created one but couldnt find the formulation. Would you be able to help if i email the details ? Thank you

[@MyOnlineTrainingHub]

Hi Jag, please post your question on our Excel forum where you can upload a sample Excel file and we can help you further: www.myonlinetraininghub.com/excel-forum

[@KashishAggarwal-xp8eh]

There is great flaw in stockhistory. It doesn't map the market events. For example, if the the stock has gone through stock split then the prices extracted shall be unadjusted, so before the stocksplit the prices will be significantly higher than the prices after stock split. Is there any way to overcome this problem.

[@MyOnlineTrainingHub]

From what you describe it sounds like it’s returning the correct prices i.e. before the stock split the price is higher than after. I don’t understand the issue.

[@kashishaggarwal5533]

@@MyOnlineTrainingHub The issue is, when you try to extract the prices and compile it in a chart, the chart moves abruptly on the day of the split. And if you try to further code it to run some analysis, the code gives a false result.

[@TheSardOz]

Thank you for another great video, I would love to see a video to create a dashboard for intraday trading, to analysis performance, like p/l , expectancy, win loss ratio.. etc! I have been finding hard to organize the data from IRESS to get those metrics!

[@MyOnlineTrainingHub]

Glad you liked it :-) intraday trading isn't my area of expertise, sorry.

[@RodneyCleek]

I've been trying to return indices like DJIA or S&P500 with no success. How can we do this? Thanks.

[@MyOnlineTrainingHub]

Hi Rodney, the STOCKHISTORY function doesn't return the whole exchange total, it returns stock prices within an exchange.

[@RodneyCleek]

@@MyOnlineTrainingHub Thank you. Great videos!

[@wkl3968]

How to remove the weekend/holiday data from evaluating?

[@MyOnlineTrainingHub]

Not sure what you're referring to, sorry.

[@djambradollo4033]

THANK YOU

[@MyOnlineTrainingHub]

My pleasure :-)

[@yahoucmay]

Heyecan verici

[@MyOnlineTrainingHub]

Great to hear!

[@matheusmendes1052]

Hello, I do have office 365, but It looks like the function STOCKHISTORY is not available....Could It happen?

[@MyOnlineTrainingHub]

Maybe you're on the semi-update channel and haven't got the update yet?

[@matheusmendes1052]

@@MyOnlineTrainingHub It looks like my office is up tô date

[@drsteele4749]

Mynda, two years on and the functionality has changed. At 15:00 onwards, when the functions looks up data for a weekend or a non-trade day, it no longer reports the prior date's value as it did for you in your video; now it reports a #VALUE! error. This is incredibly frustrating! We want it to report the prior date's value. What a pain.

[@MyOnlineTrainingHub]

Oh yes, that is annoying! I'll ask Microsoft why that changed.

[@paulasavastano971]

I am getting errors when I try to do anything on the spreadsheet "Excel ran out of resources while attempting to calculate one or more formulas, As a result these formulas can't be evaluated" Please help.. Thx

[@MyOnlineTrainingHub]

Hi Paula, it could be a myriad of things causing this. Very difficult to diagnose without seeing the file. If possible, please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@wkl3968]

not sure why my excel 365 cannot work with Stockhistory even I downloaded the workbook/example...

[@MyOnlineTrainingHub]

Probably needs updating to get the new function.

[@wkl3968]

@@MyOnlineTrainingHub looks like I need to join the insider program in order to make the stockhistory work. is working now and I enjoy your sharing.

[@kennethpelt158]

Love the videos. Is there a way in excel to get stock data from a url? I’m trying to import data from www.finviz.com.

[@MyOnlineTrainingHub]

Thanks, Kenneth! The STOCKHISTORY function gets data from refinitiv. If you want to get data from another site you could try Power Query: www.myonlinetraininghub.com/get-data-from-the-web-with-power-query

[@ghostrider5074]

The sort function didn't sort correctly from most current to oldest, is there a fix?

[@MyOnlineTrainingHub]

Perhaps you can post your question in our Excel forum where you can share your file and I can see what the problem is: www.myonlinetraininghub.com/excel-forum

[@leoooo11646]

Able to connect to hong kong stock market ?

[@MyOnlineTrainingHub]

Yep :-)

[@francescotagliavento8081]

Hi Mynda, why Can't find stockhistory?

[@MyOnlineTrainingHub]

It's only available in Microsoft 365 versions of Excel.

[@francescotagliavento8081]

@@MyOnlineTrainingHub thank you. When would it be available for eveyone?

[@MyOnlineTrainingHub]

New features don't get added to earlier versions. You'd need to upgrade to the 365 license.

[@ricos1497]

Currency is very interesting. A lot of companies I work for pay an annual subscription for this type of data in the UK (in order to automatically upload to their finance system). It'll be interesting to see if this data access will be free in the future or we'll have to pay to access various data types in future as an addition to the 365 subscription once Microsoft hold the monopoly on people accessing the data. I like the idea behind the data types, but it seems strange that it's not presented via the data model/power query. It would seem that refreshable data like this would be better served via the refresh all button rather than the busy notification. Hopefully it paves the way for better interaction with the data model than the cube functions and perhaps the ability to have a function to change number formatting in the spill formula

[@MyOnlineTrainingHub]

I'm not aware of any plans for Microsoft to charge for this service in the future, and since you're already paying a 365 subscription, you'd hope not. I agree, more functions with FNVs would be great and data types in the data model makes sense. There are lots of new things coming to Excel, so it certainly is interesting times ahead.

[@nightfrogs]

Great video but the real Bitcoin is BTC, definately NOT BCH!!! (offensive to Bitcoin community)

[@MyOnlineTrainingHub]

Thank you! BCH is the symbol provided by the data types provider. Not sure why it’s BCH and not BTC 🤷‍♀️

[@nightfrogs]

@@MyOnlineTrainingHub BCH is for 'Bitcoin Cash', a forked version of the original Bitcoin blockchain

[@user-ln9yf1cl2t]

Why bother with making a video when you keep doing so fast that people can't understand . You are gloating over your successful completion of this function not teaching step by step to those who want to learn. Many other features you use but you don't bother to talk about them at all. This is not worth learning from .

[@MyOnlineTrainingHub]

Sorry to hear the pace was too fast. You can use the cog icon in the bottom right of the video to slow down the playback speed. Unfortunately, I can't start at the very beginning for everything I cover as the video would be too long. There has to be a point where I assume knowledge. If you have questions, I'm happy to help in our Excel forum: www.myonlinetraininghub.com/excel-forum Or perhaps my Excel for Beginners playlist would be helpful: kzbin.info/www/bejne/g5-qepSnpdZ_irc

[@grengren2]

What a waste of time - other systems have had better than this for years ! Good luck making money - lol

[@MyOnlineTrainingHub]

I'm not sure what is a waste of time exactly? Other systems? How much do these other systems cost? It's difficult to consider your point without any details.

[@pranlalmandalia729]

Please provide me the formula like GOOGLE Finance (A3,high52) and GOOGLE Finance ( A3, low52) for 3 month maximum high price formula and 6 month maximum high price formula GOOGLEFINANCE (A3,. )

[@MyOnlineTrainingHub]

Please see this written tutorial on STOCKHISTORY function for the arguments. www.myonlinetraininghub.com/excel-stockhistory-function If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@edwardchau5818]

The STOCKHISTORY function 'autopopulates' when I start typing in the first few letters, but when I enter the whole formula including sort, I get '0'. Do I need to update something?

[@MyOnlineTrainingHub]

Hard to say without seeing your formula. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum