Good Morning! I am a 365 office subscriber and this formula is not available. As i have my office in Spanish, i tried the "Spanish formula HISTORIALCOTIZACIONES" i did not work either I wanted to practice this amazing video that you published. If you can help me, i would appreciate. Thank you..
i prefer using the stock icon in microsoft excel in the ribbon its easier for me the other way is more complicated which would you prefer using long way or the easiest way
Just found this recently, and I am excited about trying out some of these features myself. Never too old (81 years old now) to learn another new trick. Thank you so much.
Leila is always adding to our knowledge "STOCK" and creating "HISTORY" with her unique teaching style and long list of videos on practically everything needed in office work! Glad to watch this neat video, thank you! 😊👍👏
Leila, you are an amazing teacher with deep knowledge, awesome content, and a wonderful style of presenting things quickly and cleanly. Well done and Big Kudos on the success of your brand!
Not sure how do you know most Indians are appreciating your work. Just noticed you highlighted India is covered like emerging market, emerging investors in India. :)
@Leila Gharani, Ma'am - Please make a series on Stock Market.. Your way of teaching (Specially Excel) is excellent and lots and lots of people has learned lot from your courses /RUclips videos. Kindly give a thought on this. Best Regards Shubham
Excellent! I have been using the Stock function from the time you posted your video on the function, and it has been perfect. Obviously, this function is so much better. I will start it today. Thank you again for all of your great videos.
Neat! I like how you made the formula more dynamic. Your mastery of Excel is impressive and does qualify you as a geek---just like it shows on the cup. :) Thank you for sharing your knowledge!
Excellent, Leila! As always, you’re a big help for me. Thanks a lot and more power to you. Stay safe, God bless you! I did not skip the ads, grateful meh here! 🤗
Thank you so much for explaining it really well. I had looked up other tutorials for this function, but failed to fully understand it. Two questions if you haven't answered them already: 1) How to align daily data for several stocks (eg I have some stocks that don't go back a year and their results are misaligned to the others). 2) the present setup shows the oldest data first, how can we set it to show today's price in the leftmost cell and then go backwards? Thanks much
@@jamesroberts5645 I now use the following to calculate stockhistory: Fill up the top cell with date and use this for returning stockvalue at only that date: =STOCKHISTORY($A$5,E1,E1,0,0,1) Where E1 is the date for which I want the stock value. Hope that helps
@@michaelren104 Thanks Michael, that worked a treat. In case you're still after an easy way to have historical data in descending order, after hitting my head against numerous walls and necking a few large bottles of fine scotch whiskey i finally found the solution. This is the formula: =TRANSPOSE(SORT(STOCKHISTORY(A5,C2,TODAY(),0,B42,B43,B44),1,-1)) Its the 'SORT' and 1,-1 at the end that does the magic. You may already have discovered this but i only just have. Lol!
Super video, Leila. You've got to be careful with applying that technique to a portfolio of stocks. Sometimes there are missing dates from trading halt orders or you've got stocks across different exchanges with different days off, especially for holidays between countries.
Fully agree - that's why we need to do the check first to make sure they all have the same dates otherwise we might be showing the wrong value for the date.
Great class. I am not able to see the stock button on my Excel version. Do I have an older version? I think I have Microsoft 365. I also want to input other stock information like MACD, RSI, Stochastics, and other technical data. Can I do that? Thank you.
Thanks Leila, indeed a useful video. Can you please help to derive the daily moving average in one cell of the last 30 days closing as that will help to compare with the current price. this function is there in Google Sheet but unable to found in MS Excel.
thank you very much for all these wonderful and valuable information you are providing And just now I was telling my mom I wanna learn and know whatever she knows from the excel sheet 😓 Because I'm an accountant.
Thanks a lot Leila for this video. It has really helped me in my trading plan/strategy. But another challenge that I have faced is when it comes to fetching data based on the one-hour time frame. The Stockhistory function gives data only on the daily, Weekly and Monthly. But I need data for hourly time frame. How can I go about that?
this function is amazing and you explained it very well. can we get adjusted close price of stocks as there is no option for that to select. Mostly calculation on stocks are done on adjusted close price basis which is downloadable from yahoo finance website. But If I am working on any research of around 50-100 companies . Can we get adjusted close price in excel with this formula?
For daily info, I just passed the array of dates outputted in the date row as the "start date" for all the other rows (it handles dynamic array). Performance might not be as good but it worked just fine for ~10 companies with 5 years of data. Also, you want to pick a ticker for the date row that has been traded consistently for your time period.
I been watching your videos lately. Thanks for all the information. It’s really helpful. I want to create an interactive dashboard for my stock portfolio. Do you have a video on that?
Hi been looking for a while now but nothing mentions if this feature is exclusive to office 365 or if it is available to the home & student/professional is this function in these? if not is there a way to create a macro that does this function?
I would like to show the year to date performance of spy as a percentage. Is there a way to use stockhistory to do that? Do you know of some other way to get excel to show me the year to date performance of spy as a percentage? Thanks so much in advance. We really love your videos. They are so helpful and they age well.
Ma'am can u make more videos on accounts? Your previous accounts videos were very clear and much easier to understand. Thank u for making such a great videos❤😊✨
Great tutorial. Thanks a lot. A question though. If you have stocks from different exchanges (for example XNYS and XBRU), the exchange have different days that the stock markets are closed/open and the timeline is not the same. Would there be a way to force the values to use a unique timeline ?
Hey Leila, awesome thanks for this! I have an issue, when the 5 year history and dates spill they dont align. It will happen with the stocks that dont have a 5 year history. Is there a work around? Thanks !
Thanks Leila. Great intro to get started with the new STOCKHISTORY function! Too bad Sparklines Data Range will not accept #SPILLED range as input. Maybe some day. Thanks for sharing :)) Thumbs up!!
Thanks for the awesome video! I'm running into a problem where I'm trying to generate stock data for YTD 2022 and it will not give me data through today June 4th. Is there a way to tell excel to use previous data if the market is closed so it will generate data for everyday even on the weekends using the previous close of the last open market day?
Hi Leila, thanks for all the videos that you post. very clear and helpful. I have a problem I am trying to create a table with only the daily %change. and i cannot figure it out. could you help me please
Good Morning (5:27AM) from chilly Maryland (23 degrees (F) ... Once again, content and presentation so good ... so very helpful ... and ... right on point to a project that I expect to undertake shortly. Question: Will the data automatically update when the file is opened? ... or ... will the user have to "Refresh All"? PS "... Well … if you just want one part of it back … you can put it inside the "Index" function …" Yes … something I would have figured out … never!
Good afternoon :) It's snowing here today! According to the Microsoft support page STOCKHISTORY values are updated once daily on the backend so when these are updated they should automatically pull through every time you open the workbook or the update takes place.
Leila, thank you for the video. I ran into an issue with commodities market data tracking from Chicago Board of Trade. Is there a way to pull this into excel live like the other stock exchanges? Thank for any guidance here.
Grab the file I used in the video from here 👉 pages.xelplus.com/stockhistory-file
Stockhistory is now available to all Office 365 subscribers!
Maybe we’ll get this in other versions in 2030 😂
But choosing a different ENDDATE instead of TODAY does not work, why? Also I think the common date periods should be solved by XL. It is a bug.
Good Morning!
I am a 365 office subscriber and this formula is not available. As i have my office in Spanish, i tried the "Spanish formula HISTORIALCOTIZACIONES" i did not work either
I wanted to practice this amazing video that you published.
If you can help me, i would appreciate.
Thank you..
i prefer using the stock icon in microsoft excel in the ribbon its easier for me the other way is more complicated which would you prefer using long way or the easiest way
I wanted to know for a job
Just found this recently, and I am excited about trying out some of these features myself. Never too old (81 years old now) to learn another new trick. Thank you so much.
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
as a 30+ year excel user, I am humbled with your knowledge and new ideas. Thank you very much!
Thanks for watching, Don!
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Leila is always adding to our knowledge "STOCK" and creating "HISTORY" with her unique teaching style and long list of videos on practically everything needed in office work! Glad to watch this neat video, thank you! 😊👍👏
Thank you, Vijay 😊
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
I was desperately looking for this function in excel. Thank you very much. Very informative
Glad it was helpful!
ruclips.net/video/_H-1-MriYD8/видео.html
Fantastic! Never in a million years could I have figured this out without your video
Glad I could help!
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Leila, you are an amazing teacher with deep knowledge, awesome content, and a wonderful style of presenting things quickly and cleanly. Well done and Big Kudos on the success of your brand!
Thank you!
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
This has made me grown one more bit today, many thanks Leila
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
I've followed your channel for about a year now. I've learned so much. Thanks.
Great to hear, Pierre!
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Fabulous, I'm looking forward to STOCKHISTORY appearing in the main Excel release channel. Thank you for the walkthrough!
You're very welcome, Robert!
Not sure how do you know most Indians are appreciating your work. Just noticed you highlighted India is covered like emerging market, emerging investors in India. :)
This video is super useful. I think you will have to put up a question and answer session for your viewers.
You are amazing teacher with mastery very impressive!
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Thank you this video. Would love to see more videos on stocks functions....
@Leila Gharani, Ma'am - Please make a series on Stock Market.. Your way of teaching (Specially Excel) is excellent and lots and lots of people has learned lot from your courses /RUclips videos. Kindly give a thought on this.
Best Regards
Shubham
Excellent! I have been using the Stock function from the time you posted your video on the function, and it has been perfect. Obviously, this function is so much better. I will start it today. Thank you again for all of your great videos.
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Neat! I like how you made the formula more dynamic. Your mastery of Excel is impressive and does qualify you as a geek---just like it shows on the cup. :) Thank you for sharing your knowledge!
Wow, thanks!
Hi Leila, Thank you for sharing your knowledge! You do an excellent job at explaining Excel and make it easy.
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Excellent function and a very good explanation as always by Leila. Well done and keep it up.
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
I learn a lot of excel with you and also an excellent English pronunciation, 2x1. Muchas Gracias
Thank you for mentioning INDIA is included in the covered exchanges
😀 It was a question in the comments of the previous stock data type video so I thought I address it here. Thank you for the first like and comment 🙏
@@LeilaGharani I raised that question on instagram and Your RUclips channel..
Found this and am excited to use the features. Thank you for your education.
Leila is the crush of all Geeks! Love you and your work! 😍
Excellent video! Clear and crisp tutorials as always!
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Excellent video tutorial, Leila! Keep it up! Thanks!
Thank you! Will do!
Excellent, Leila! As always, you’re a big help for me. Thanks a lot and more power to you. Stay safe, God bless you! I did not skip the ads, grateful meh here! 🤗
Thank you 😊
Extremely Marvelous Tutorial.. I have been following up every time.. You are Magnificent Leila
Happy to hear that!
This is a great Video Presentation. Very useful tips and tricks.Thank you Leila.
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Very instructive and informative video. Thank you so much for share it.
This is a brilliant add-on to Excel! Thanks!
Awesome content..explained in most easiest way..Thankyou..
Thank you so much for explaining it really well. I had looked up other tutorials for this function, but failed to fully understand it. Two questions if you haven't answered them already: 1) How to align daily data for several stocks (eg I have some stocks that don't go back a year and their results are misaligned to the others). 2) the present setup shows the oldest data first, how can we set it to show today's price in the leftmost cell and then go backwards? Thanks much
I second this question? Would save a lot of time adjusting ranges etc. Thanks for the great video, very informative! :)
@@jamesroberts5645 I now use the following to calculate stockhistory: Fill up the top cell with date and use this for returning stockvalue at only that date: =STOCKHISTORY($A$5,E1,E1,0,0,1) Where E1 is the date for which I want the stock value. Hope that helps
@@michaelren104 Thanks Michael, that worked a treat.
In case you're still after an easy way to have historical data in descending order, after hitting my head against numerous walls and necking a few large bottles of fine scotch whiskey i finally found the solution. This is the formula: =TRANSPOSE(SORT(STOCKHISTORY(A5,C2,TODAY(),0,B42,B43,B44),1,-1))
Its the 'SORT' and 1,-1 at the end that does the magic. You may already have discovered this but i only just have. Lol!
Thank you, Leila. You are awesome!
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Looking forward to see more Formative stock functions which can generate reports
Tank you Leila for your help and may God bless you
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Super video, Leila. You've got to be careful with applying that technique to a portfolio of stocks. Sometimes there are missing dates from trading halt orders or you've got stocks across different exchanges with different days off, especially for holidays between countries.
Fully agree - that's why we need to do the check first to make sure they all have the same dates otherwise we might be showing the wrong value for the date.
Leila, as always, brilliant. I knew this function, but I don't remember whether I saw it in your previous videos or in your courses. Thank you.
ruclips.net/video/20kqmtzok8I/видео.htmlsi=nnOpAEpBY8gqJl-d
Very Great introduction. Will get it and try soonest. Amazing
Thank you Leila, this was VERY helpful.
Nice video... thank you. Is there a function to reverse the order of the date from the newest to the oldest? Thx
I love this function! Great video, thanks for sharing.
Finally got around to setting this up, thank you.
Is there a way to reverse the date sequence from newest to oldest?
Great video. Thanks.
Great class. I am not able to see the stock button on my Excel version. Do I have an older version? I think I have Microsoft 365. I also want to input other stock information like MACD, RSI, Stochastics, and other technical data. Can I do that? Thank you.
Love your videos. Q.why are some stock prices not shown, e.g.Lloyds Bank, BT etc on the London stock exchange.?
Thank you so much for this… it was extremely helpful. Good job!
You're very welcome, Steve!
Great mam very knowledgeable, thanku you also suggest me delivery quantity of stocks for one week
Great video...always learning from your work.
I appreciate that!
Thanks Leila, indeed a useful video.
Can you please help to derive the daily moving average in one cell of the last 30 days closing as that will help to compare with the current price.
this function is there in Google Sheet but unable to found in MS Excel.
Thank you so much - great video as are the others!
thank you very much for all these wonderful and valuable information you are providing
And just now I was telling my mom I wanna learn and know whatever she knows from the excel sheet 😓
Because I'm an accountant.
Thank you for this video, keep growing.
Thanks a lot Leila for this video. It has really helped me in my trading plan/strategy. But another challenge that I have faced is when it comes to fetching data based on the one-hour time frame. The Stockhistory function gives data only on the daily, Weekly and Monthly. But I need data for hourly time frame. How can I go about that?
Gr8 video - how do I get data for monthly close for past 12 to 24 months i.e., last day of each month and do a % change ?
this function is amazing and you explained it very well. can we get adjusted close price of stocks as there is no option for that to select. Mostly calculation on stocks are done on adjusted close price basis which is downloadable from yahoo finance website. But If I am working on any research of around 50-100 companies . Can we get adjusted close price in excel with this formula?
Amazing work. Thank you
Just what I was looking for, thanks a lot.👌
For daily info, I just passed the array of dates outputted in the date row as the "start date" for all the other rows (it handles dynamic array). Performance might not be as good but it worked just fine for ~10 companies with 5 years of data. Also, you want to pick a ticker for the date row that has been traded consistently for your time period.
Thanks for sharing your tips!
Terrific explanation. thank you
thank you for such wonderful trainings!! very clear and easy to follow. I'm able to replicate effectively.
Wonderful!
I been watching your videos lately. Thanks for all the information. It’s really helpful. I want to create an interactive dashboard for my stock portfolio. Do you have a video on that?
Nice tutorial, nicely explained.
Thank you!
Thank You! Is there similar approach to download daily stock options prices?
Really really very very amazing function great 👍👍
It really is!
Hi been looking for a while now but nothing mentions if this feature is exclusive to office 365 or if it is available to the home & student/professional is this function in these? if not is there a way to create a macro that does this function?
Thank you Leila, excellent video. How do i get Commodity prices, like Spot Gold, Silver, Maize, Coffee, etc... ?
Can you please explain how we can get the closing price o stocks for consecutive days (say 10 days) instead of months? Thank you!
I would like to show the year to date performance of spy as a percentage. Is there a way to use stockhistory to do that? Do you know of some other way to get excel to show me the year to date performance of spy as a percentage? Thanks so much in advance. We really love your videos. They are so helpful and they age well.
This is awesome - can it be used for commodity stock pricing?
Very interesting and useful information. Thanks a lot
Man, I had this in the wrong Playlist!
Great video...thanks for sharing...Leila...👍
You are so welcome!
Ma'am can u make more videos on accounts? Your previous accounts videos were very clear and much easier to understand. Thank u for making such a great videos❤😊✨
Thanks, You made that look easy.
Great tutorial. Thanks a lot. A question though. If you have stocks from different exchanges (for example XNYS and XBRU), the exchange have different days that the stock markets are closed/open and the timeline is not the same. Would there be a way to force the values to use a unique timeline ?
Excellent video & excellent formula! It would be nicer for the formula to show also the number of outstanding shares though.
Hey Leila, awesome thanks for this! I have an issue, when the 5 year history and dates spill they dont align. It will happen with the stocks that dont have a 5 year history. Is there a work around? Thanks !
Worked it out, created a duplicate and used: =INDEX(BW6:EE6,1,COUNTA(BW6:EE6))
You look good in this new look ❤️
Thanks 😅
really beautiful video. Amazing
Thank you!
I need this to be this way for my double bottom strategy code.
Mam you are looking gorgeous in your office. Stay blessed.
Thanks Leila. Is there a way to get the monthly info at month end rather than month start?
Thanks Leila. Great intro to get started with the new STOCKHISTORY function! Too bad Sparklines Data Range will not accept #SPILLED range as input. Maybe some day. Thanks for sharing :)) Thumbs up!!
Loved your new setup 🤘
Looking Forward for more Excel Hacks
Thanks for this informative video. Sadly not in Excel 2016
Hellow Leila, I watch quite a bit your tutorial for stock, can you suggest how to down load portfolio history from Robinhood.
Thanks for the awesome video! I'm running into a problem where I'm trying to generate stock data for YTD 2022 and it will not give me data through today June 4th. Is there a way to tell excel to use previous data if the market is closed so it will generate data for everyday even on the weekends using the previous close of the last open market day?
this is like the reviews for the latest GPUs/CPUs, most of the people don't have access to these nice features of the coming months...
Always good to know what's coming though 😊
Leila ,Do u know U r so amzaing ... and keep rocking..enjoy !
Hi Leila, thanks for all the videos that you post. very clear and helpful. I have a problem I am trying to create a table with only the daily %change. and i cannot figure it out. could you help me please
Awesome, thanks for this!
Great vid Leila. Any chance there’s a similar function for currencies?
Yes. Same function: =STOCKHISTORY("EUR/USD",DATE(2021,1,1),TODAY(),0)
Great presentations. I stuck need help with finding dollar value
Mam if possible pls upload historical data get time basis
Thanks for sharing!! But how to deal with the situation that there are new stocks which with shorter stock history than the others?
Good Morning (5:27AM) from chilly Maryland (23 degrees (F) ...
Once again, content and presentation so good ... so very helpful ... and ... right on point to a project that I expect to undertake shortly.
Question: Will the data automatically update when the file is opened? ... or ... will the user have to "Refresh All"?
PS
"... Well … if you just want one part of it back … you can put it inside the "Index" function …"
Yes … something I would have figured out … never!
Good afternoon :) It's snowing here today! According to the Microsoft support page STOCKHISTORY values are updated once daily on the backend so when these are updated they should automatically pull through every time you open the workbook or the update takes place.
timely video! 🚀🚀🚀
Excel is getting more and more stocks friendly
Thinking of taking one of your online courses. Do you speak as quickly as you did in this video?
No, my course lectures are slower. But you can always speed them up if needed :)
Leila, thank you for the video. I ran into an issue with commodities market data tracking from Chicago Board of Trade. Is there a way to pull this into excel live like the other stock exchanges? Thank for any guidance here.