Get Stock History in Excel with STOCKHISTORY Function

Today I'm going to show you a new

function

in Excel that allows you to obtain historical

stock

information. You can get daily, weekly and monthly information, so you can easily observe the trend of

stock

s, how prices develop over time, for example. This all requires a simple

function

, but I'm going to modify it a little so that you can get a report like this, where we can see the trend of multiple stocks in a single view. But before we begin, a quick thank you to AlchemyJ for sponsoring today's video. They recently released their latest addition to their portfolio, it's called AlchemyJ Excel Library.

And just to quickly tell you what it is, it is an Excel add-in that provides over 90 extended functions to Excel to perform different tasks. Now, best of all, this Excel library is available to download for free; You can find a download link in the description of this video. Now, I'm going to talk more about them towards the end, so stay tuned. Now let's look at the trends of the stocks I selected to see which ones are going up and which ones are going down. (upbeat music) Okay, I've already added the ticker symbol for the companies I want to analyze here.

However, before we begin, two important points. Number one, STOCKHISTORY is currently available in the beta channel, so it's generally not available to everyone who has Office 365, but if you're patient, it will be available as long as you have Excel for Microsoft 365. Number two, you're probably wondering which exchanges are currently supported. Well, here I have the link. Also available in the description of this video, it will take you to this Microsoft support page and tells you which exchanges are currently covered. So if we scroll down, for example, you can see that India is covered. Ok, now that we have covered these important points, let me show you how stock

history

works.

Once we understand this feature better, we will create this report where we will show the historical price trend against each company, and we will also show a mini line chart in each cell, so that we can take a look at the trend in one place. The function you need to get historical stock information is called STOCKHISTORY. What you need is the action, this is the ticker symbol, now you can put it in quotes or just make a cell reference. In this case, I have it in the cell, so I'm going to reference it. Next you need the start date.

There are different ways to give the date, you can reference a date that is in a cell, you can type a date as text. So, for example, let's go with 1/8/2020. If you don't give it an end date or any other information, what will happen is that the end date will be your start date. Notice the spill function, it created two columns automatically, one is called date and the other is the closing price. All I had to do was write a single function in this cell, and everything else came automatically. Now let's look at the other parameters we can add here.

So for the end date, we can use the today function, so today, open parenthesis, close parenthesis, and then we have the interval. By default, it is daily, so you get the daily closing price. But you can also take a look at this weekly or monthly just by changing it to one or two. But I'm going to use zero per day. Next is whether you want to have a header or not. So the default is to show the header, but if I don't, let me show you what happens. I'm going to drop the formula right now. So if the square bracket is closed, press Enter, and those date and price headers you had are gone.

But now I have daily information up to today's date. Okay, now let's go back and change this to one, when I press Enter, the headers appear there. So what other options do we have? Well, all these properties here are different things that you can recover. So zero is for the date. That's the date column here, one is nearby, and remember, these were the default values. So if I want more than just that, if I use 0.1, and let's say, I also want to get the high level, then that's a three, and I also want to get the volume, that's a five.

I'm just going to type in these numbers and then when I press Enter, I get the information I requested using these indexes. If I want to have this weekly or monthly, I just have to go to the right place, so the interval was here and I can change this to two. Let's go with the monthly. Well, that's how STOCKHISTORY works. Now what I prefer to do instead of writing the dates this way and hardcoding them, I prefer to use the date function, which is more flexible here, we can give it the year, month and day, and you also won't have any problems if you share your books with people with different locales and languages.

Another advantage of using the date function is that you can also reference cells for the different parameters. So here, I would write this as 2020, month one, and the day would be eight, I close the square bracket, I press Enter, everything works, but I can also use cell references, instead of writing it like this. Now, if you already have your date somewhere in a cell, you can reference it too. So, for example, for our actions here, let's say we want to go back two years and we want to get monthly information. Let's first calculate the date that corresponds to two years ago from today's date.

So let's be flexible and connect it to today's date. Well, one function I can use here is the edit function, all you need is the start date. So that's going to be today's function with the open bracket and the closed bracket, and the number of months I want to go back. So I'm going to use minus 24, close the square bracket, press Enter, and that's my date, it shows it as a number, I'm going to change it to a short date instead. This will be my start date here. But I'm in this cell, I can't change it, notice the formula is grayed out, I have to go to the top left corner and now I can change this reference and reference this cell instead, and then when I press Enter, I get all the stock market information from the last two years, until today.

And this is monthly information now. In case you are only interested in the date and closing, I can get rid of this set of extra parameters. I think they're the last two and I go in and I just have these. If you get errors, make sure your ticker symbol is correct and in case you want your data from another exchange, you can add it before the ticker symbol. So by default the stock exchange used is NASDAQ, but you can change it here. So let's say I want to get the Wiener Boerse, I'm going to use . Okay, so I'll use Control + Z to reverse that.

Well, so far, so good. Now that we understand how the stock

history

symbol works, let's enter the historical stock information for the last two years for all of them on a monthly basis and put it in front of each of these companies. The problem here is that the function spills vertically, I want to have everything horizontally. Now, in order to put everything horizontally and ensure that the closing price corresponds to the correct date, we need to make sure that we are working with identical dates for these stocks. In this case, since I am working with monthly stock information, they will be identical.

So if I, for example, let's copy this and compare it with Google's. I'm going to paste the formula here and I'm just going to drag it into Google, hit Enter, and now I'm going to get the historical Google stock prices on a monthly basis. Note that the dates here are identical. So now all I have to do is transpose this information. So let's put this inside the transpose function and see what we get. When I press Enter I get the date and closing, in this case it is for Microsoft. I'm getting close, but I want my formula in a way that I can just drag it and apply it to the rest as well.

So let's zoom in on this. Remember, in the last arguments here, I can define the parameters I want. So this one here, the zero is for the date. If I don't show the date, I'll just hide it, instead I'll just show the closing, that's the one, right? That's the one here, when I press Enter, I get the closure. Remember, there was also the parameter about whether I want to show headers or not, that was this one. So I'm going to go without a header, which is a zero. Now when I press Enter I only have the Microsoft prices.

This means I can drag my formula down, once I make sure I've fixed the correct cell references, then A3 here, should be a relative reference, because I want to drag it down, I want it to go down, but A1 should be a absolute reference, I'll press F4 and fix the rest of it right, so when I hit Enter, I can drag this down and I have the historical stock prices in one place. So let's make sure the cells are wide enough to display our numbers. However, what is missing are my dates. I don't want to hardcode them, I want them to be dynamic too.

So can I copy this? Let's paste it here. I'm going to leave this to Microsoft. It doesn't really matter which of these stocks you have. Ultimately, here I only want to show the dates and not the closing price. So remember, here, instead of one, I'm going to use a zero. Now, does this work? When I press Enter, it doesn't work, because it doesn't just give dates, it needs to give dates with the closure with the volume or with something else. But currently, it just doesn't provide dates on its own. So this means I have to add something else here.

But in this case I can't, right? I get the spill error, because it's going to give me two rows, I already have information here. Well, if you only want to retrieve a part, you can put it inside the index function. The array is this, then we just have to tell it what row number to return. I want everything in the front row because that's where my date is. And my column, well, I want to return each column, so I just have to close the square bracket, press Enter and I get my dates, right? So all I did was put this inside the index function and use the row one argument to retrieve the first row, okay?

So far so good, I'll put them in bold. Well, as a last step, I want to show the trend of each of these stocks. So let's make the cells a little wider so we can see the trend better. Now all I have to do is go to Insert and add a sparkline to it. I'll go with the Line sparkline. My data range is this, and the location is fine. And I have the sparkline. The great thing about the sparkline is that it acts like a formula, I can drag it down and each one of them references this range right here.

Now I can make adjustments to this as well. Go to Sparkline, adjust the sparkline color if you want, the line weight and size, I'll just make it a little thicker. I also want to add a marker color for the highest point. So let's go with a green color here and a red color for the low points. Well, I can quickly see which stocks are rising and which are falling. Now, if you're using the stock data types, which is something we looked at in a previous video, if I go to Data here, we have the stock data types, I can convert them to official stocks, everything works as before.

So, you can work with stock data types, or you can just work with texts, by typing the stock symbol manually here. If you are interested in learning more about stock data types, watch the video above. I will also add the link to the description. As you can see, it's very easy to get historical stock information in Excel and organize it any way you need. Now I have a question for you. Do you work a lot with large numbers, with databases, JSON and XML? If so and you would like to use your familiar Excel environment to work with them, then the AlchemyJ Excel library can help you.

It was created to fill the gaps of what Excel is missing in this regard. Normally you would have to write your own VBA code to overcome Excel's limitations, but with this Excel add-in you get additional features so you don't have to code it yourself. For example, this function here counts the number of times a pattern occurs in a string using a regular expression. You can search a range of cells in multiple rows and columns. The function returns the frequency with which it found a pattern in the selected range. The AlchemyJ Excel library also has a variety of functions for large numbers to overcome the 15-digit precision limitation.

For example, with this function you can compare two large numbers. If the result is one, it means that the first number is greater than the second. Otherwise, it will return minus one, if the second is greater, or zero if the numbers are equal. There are now many more useful functions like these in our AlchemyJ Excel library. Check it out if you are interested. The link is in thedescription of this video. Let me know what you think about it. Thanks for watching, see you in the next video. (upbeat music)