Historical Stock Quotes with Excels STOCKHISTORY Function

Welcome to historical stock quotes with stock history my name is jeff i’m glad you’re here let’s just jump right in our goal is to allow the user to type in a ticker or or company name into this input cell uh enter a start and end date and we would like excel to automatically retrieve the closing prices for all the dates and then also display those in a chart

Okay so can we do this sure sure and here’s how first we’re going to convert this text value into a stock data type the way that we do that is by selecting the input cell going to data and stocks and now as you can see this change from the ticker symbol into this company name and this is retrieving rich data about this company and we can get a preview of it

By clicking the icon and we can see this we can scroll down we can also add additional columns as desired with to retrieve some related information but for now we’ve converted that text string into a stock data type so the next thing that we’re going to do is use the stock history function to retrieve all of the closing prices okay so the way that we do that

Is equals stock history and this function has many options and many arguments the first one that it needs is the stock okay and so we just point it to our input cell which is b4 comma the next argument is the start date which is here comma the next argument is the end date which is here comma the next argument is the interval do we want daily weekly or monthly

Closing prices in this case i’ll take daily so i’ll use 0 and comma and then the next argument is headers do we want headers yes or no in this case i don’t want any headers those are the the column labels so i’m going to use zero close the function and now if it works let me go ahead and hit enter and yes yes we got it okay the stock history function retrieved

The closing prices for all the closing dates between our start and end dates and this is dynamic so let’s say we want to grab everything and through january 10th 2020 okay that’s what it retrieves we want to go through 6 30 20 20. that’s what it retrieves yes okay and so we get the idea let me go ahead and set this back to 8 1 20 20. and now what’s cool is we can

Type in a ticker symbol so let’s try another ticker symbol and that is done let’s try company name and that is done so pretty cool dynamic solution let me go ahead and change this back to microsoft there we go now what if we want to display this in a chart well we can just select any cell within our data range and go to insert pick your chart type i’m going to go

The line chart and now we’ve got that and there’s a couple of notes about making this dynamic so if we just wanted to create this one chart for this set of data we could easily just change the chart title manually and call it good but if we wanted it to be more dynamic then let’s give this a dynamic chart title the way that we do that is select the chart title

Go to the formula bar hit equals and then just point it to this cell we hit enter and now we’ve got a dynamic chart title so when we change this to tesla we’re good if we change this to apple then we’re good okay let’s go ahead and go back to microsoft and we’re good now there’s one other note about making this dynamic okay if we look at the data source range

Currently used by the chart and we can do that by going here and here we can see that it goes through row 156 right and that is right here so it’s included or it’s created the chart based on the current values okay so the question becomes what happens if there are more rows next time i want to do an update what if i want to go to september 1st 2020 hit enter

Did that make it into the chart no if i select this and scroll down i can see that those new rows are excluded and if i go here and select data i can see that it’s still locked into 156. so to solve for that we’re going to use some defined names and the way that we do that let me set this back by the way the way that we do that is go to formulas name manager

New now i’m going to set up a descriptive name and i’m going to call this chart labels and this name and this name is going to refer to the date column so it’s the first column within that spill range or within those results that are returned so to do that i’m going to use the index function and i’m going to need to keep stock history exclamation and instead

Of pointing it to b7 i want to point it to the formula cell the cell that has the stock history function in our case that is b10 and instead of referring to the single cell b10 i want to reference the entire results that’s called the spill range so to do that i’m going to use the spill reference operator the pound or hash and then i’m going to go with comma 0

Which means all rows in that range and then comma 1 which means the first column within that range okay so this name chart labels is going to refer to the first column within this spill range so i click ok and now i’ve got chart labels and now i want to do something similar to retrieve the chart data so i click new i’m going to call my name chart data that’s

Going to be equal to i’m going to wrap the index function around all of this instead of pointing to b7 i need it to point to the formula cell that is b10 i need to include the spill reference operator 0 for all rows 2 for the second column and ok and now i’ve got chart data chart labels click close and now i just need to update this chart source data so i go to

Chart design select data so here for the legend entries i click edit and now i want to change this a1 style reference okay that’s locked into c10 to c156 i want to swap that out for my chart data and note that we’re going to need to keep this worksheet name and the exclamation point worksheet name with the exclamation point we’re going to need to keep that in

Our formula so we click ok and now we’re going to edit the axis labels as well so edit again i need to keep the worksheet name and the exclamation but i’m just going to replace that a1 style range reference with our name chart labels and ok click ok and now we should be good to go so now if i change this to 9 1 20 20 and hit enter those new values that looks

Like it works should be included and if we scroll down we can confirm yes those new rows are included so pretty cool now we can go and say we want to grab um this company through 6 30 20. got it maybe we want to go with this company through 91 2020 and got it okay pretty cool so um anyway i hope hopefully that helps uh and um yeah thanks for joining me have a

Great day this video is a production of excel university

Transcribed from video
Historical Stock Quotes with Excel's STOCKHISTORY Function By Excel University