How to Use the STOCKHISTORY Function in Excel 📈

Подписаться 2,7 млн

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

50% 1

Link to AlchemyJ's Excel Library: www.alchemyj.io/download
The Stock History function in Excel allow you to easily pull historical stock data into Excel.
Easily pulling stock data into Excel has been one of the most requested features. With the brand new STOCKHISTORY function this is now possible. It fills the gaps in the Stocks data type by providing access to historical data.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/stockhistor...
Here's what you'll explore:
STOCKHISTORY Function Explained: Learn about this new Excel function that fetches historical stock information, including daily, weekly, and monthly data.
Creating Comprehensive Reports: Discover how to twist this simple function to create visually appealing reports showcasing trends for multiple stocks at a glance.
Setting Up STOCKHISTORY: Understand the prerequisites, including Excel version requirements and supported exchanges.
Function Parameters and Customization: Get to grips with different parameters like start and end dates, intervals, and what data points (like high, low, volume) you can retrieve.
Dynamic Dates for Flexibility: See how using dynamic dates with functions like TODAY and EDATE enhances the flexibility and accuracy of your stock analysis.
Visualizing Trends with Sparklines: Learn how to insert sparklines to visualize stock trends directly within your Excel cells.
STOCKHISTORY is available to Microsoft 365 Subscribers.
00:00 How to Get Historic Stock and Currency Information in Excel
01:08 How to Use STOCKHISTORY in Excel
05:15 How to Use Dynamic Dates with STOCKHISTORY in Excel
08:04 How to Prepare History Stock Data for Multiple Stocks in Excel
12:26 How to Use Sparklines to Visualize Trends with STOCKHISTORY in Excel
14:12 Sponsor AlchemyJ
LINKS to more videos about New Excel Functions in Office 365: • Excel for Office 365 &...
★★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This video is sponsored by AlchemyJ
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

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

2 авг 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

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

Grab the file I used in the video from here 👉 pages.xelplus.com/stockhistory-file Stockhistory is now available to all Office 365 subscribers!

@6toolbaseball 3 года назад

Maybe we’ll get this in other versions in 2030 😂

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

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.

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

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

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

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

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

I wanted to know for a job

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

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.

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

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! 😊👍👏

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

Thank you, Vijay 😊

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

as a 30+ year excel user, I am humbled with your knowledge and new ideas. Thank you very much!

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

Thanks for watching, Don!

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

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.

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

@ManojKumar-sk3fw 3 года назад

I was desperately looking for this function in excel. Thank you very much. Very informative

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

Glad it was helpful!

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-_H-1-MriYD8.html

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

Hi Leila, Thank you for sharing your knowledge! You do an excellent job at explaining Excel and make it easy.

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

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!

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

Thank you!

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

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

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

Thank you 😊

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

Excellent video! Clear and crisp tutorials as always!

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

Excellent function and a very good explanation as always by Leila. Well done and keep it up.

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

I love this function! Great video, thanks for sharing.

@67sean1310 Год назад

Fantastic! Never in a million years could I have figured this out without your video

@LeilaGharani Год назад

Glad I could help!

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

Found this and am excited to use the features. Thank you for your education.

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

Fabulous, I'm looking forward to STOCKHISTORY appearing in the main Excel release channel. Thank you for the walkthrough!

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

You're very welcome, Robert!

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

This video is super useful. I think you will have to put up a question and answer session for your viewers.

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

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!

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

Wow, thanks!

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

Just what I was looking for, thanks a lot.👌

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

Thank you this video. Would love to see more videos on stocks functions....

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

@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 /RU-vid videos. Kindly give a thought on this. Best Regards Shubham

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

Leila is the crush of all Geeks! Love you and your work! 😍

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

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.

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

Excellent video tutorial, Leila! Keep it up! Thanks!

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

Thank you! Will do!

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

This is a brilliant add-on to Excel! Thanks!

@chidieberenjoku8143 Год назад

This has made me grown one more bit today, many thanks Leila

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

Thank you so much - great video as are the others!

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

Thank you Leila, this was VERY helpful.

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

This is a great Video Presentation. Very useful tips and tricks.Thank you Leila.

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

I learn a lot of excel with you and also an excellent English pronunciation, 2x1. Muchas Gracias

@williama.rivera9414 2 года назад

Very instructive and informative video. Thank you so much for share it.

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

I've followed your channel for about a year now. I've learned so much. Thanks.

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

Great to hear, Pierre!

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

Tank you Leila for your help and may God bless you

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

@mariamnassar2402 Год назад

You are amazing teacher with mastery very impressive!

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

Loved your new setup 🤘

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

Very interesting and useful information. Thanks a lot

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

thank you for such wonderful trainings!! very clear and easy to follow. I'm able to replicate effectively.

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

Wonderful!

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

Thanks, You made that look easy.

@joysondsouza148 Год назад

Thank you for this video, keep growing.

@IbadassI Год назад

Awesome, thanks for this!

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

Extremely Marvelous Tutorial.. I have been following up every time.. You are Magnificent Leila

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

Happy to hear that!

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

Very Great introduction. Will get it and try soonest. Amazing

@ckteran 3 месяца назад

Thank you, Leila. You are awesome!

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-20kqmtzok8I.htmlsi=nnOpAEpBY8gqJl-d

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

timely video! 🚀🚀🚀

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

Amazing work. Thank you

@deepanshugarg475 8 месяцев назад

Awesome content..explained in most easiest way..Thankyou..

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

Great video...always learning from your work.

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

I appreciate that!

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

Terrific explanation. thank you

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

Awesome video, thanks

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

Excelent lesson.

@ManojKumar-sk3fw 3 года назад

Thank you for mentioning INDIA is included in the covered exchanges

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

😀 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 🙏

@ManojKumar-sk3fw 3 года назад

@@LeilaGharani I raised that question on instagram and Your RU-vid channel..

@savageplanets Год назад

Thank you so much for this… it was extremely helpful. Good job!

@LeilaGharani Год назад

You're very welcome, Steve!

@umeshchandgond6985 Год назад

Thanks This video helped me a lot

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

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❤😊✨

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

You Rock Girl!

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

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.

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

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.

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

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 ?

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

Thank you, I like it.

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

Nice tutorial, nicely explained.

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

Thank you!

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

Great video...thanks for sharing...Leila...👍

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

You are so welcome!

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

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?

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

Really really very very amazing function great 👍👍

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

It really is!

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

Looking forward to see more Formative stock functions which can generate reports

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

Very cool Leila!!

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

😊

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

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.

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

This will change my life! Thankyou 🙏

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

Our pleasure :)

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

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-_H-1-MriYD8.html

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

Nice. TQVM.

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

really beautiful video. Amazing

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

Thank you!

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

Loved it❤❤❤

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

👍

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

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

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

Excellent video & excellent formula! It would be nicer for the formula to show also the number of outstanding shares though.

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

Right on time. Thanks

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

Perfect!

@MohamedIbrahim-sv9mk 3 года назад

You look good in this new look ❤️

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

Thanks 😅

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

Woow thank you for sharing.

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

My pleasure

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

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

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

Mam you are looking gorgeous in your office. Stay blessed.

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

Excellent

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

Finally got around to setting this up, thank you. Is there a way to reverse the date sequence from newest to oldest?

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

thx a lot

@sahrasassy5982 Год назад

I need this to be this way for my double bottom strategy code.

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

Looking Forward for more Excel Hacks

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

Excel is getting more and more stocks friendly

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

Leila ,Do u know U r so amzaing ... and keep rocking..enjoy !

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

Thanks for this informative video. Sadly not in Excel 2016

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

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

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

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.

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

Thanks for sharing your tips!

@rayschrijvers9249 Год назад

Nice video... thank you. Is there a function to reverse the order of the date from the newest to the oldest? Thx

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

Good Idea

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

This is awesome - can it be used for commodity stock pricing?

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

Thanks Leila. Is there a way to get the monthly info at month end rather than month start?

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

Great presentations. I stuck need help with finding dollar value

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

Love your videos. Q.why are some stock prices not shown, e.g.Lloyds Bank, BT etc on the London stock exchange.?

@hazemgheith9991 Год назад

Thank You! Is there similar approach to download daily stock options prices?

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

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?

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

Great to see this data available with an Office license. Is there anything like that provided by Microsoft for PowerBI?

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

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.

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

thanks..

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

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 !

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

Worked it out, created a duplicate and used: =INDEX(BW6:EE6,1,COUNTA(BW6:EE6))

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

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?

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

Leila, thanks for the video. Is it possible to do the same for energy futures? i.e. Diesel, gasoline, crude oil

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

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

I second this question? Would save a lot of time adjusting ranges etc. Thanks for the great video, very informative! :)

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

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

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

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

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

Thanks for the excellent expose to STOCKHISTORY Function. I have a question in regard to this. Some of my Stock actually doesn't start at the same time. When I try to do the above, the number of entries are different when I use a monthly 2 years data (from 9/1/2019). To be specific, XNAS:UPST will give you the #N/A error for the 11/1/2020 entry but will have all other prices up to 9/1/2021. My question is how do I actually make the data and dates to be placed in the correct dates at the top which starts at 9/1/2019 an ends with 9/1/2021? Secondly, Why does it provide the #N/A error? Thanks in advance.