Broadway Gypsy asks if there is a way to create a "minute by minute changing up/down stock price arrow in Excel"?
In this video, Bill shows how to use Icon Sets and the SIGN function to add up/down arrows next to the stock prices.
He then uses the new Microsoft 365 data types and a tiny bit of VBA code to build a minute-by-minute database of stock prices.
Table of Contents
(0:00:00) Welcome
(0:00:47) Adding Up/Down Arrows
(0:03:06) Using Data Types to pull current stock price
(0:06:16) VBA to update every minute
In this video, Bill shows how to use Icon Sets and the SIGN function to add up/down arrows next to the stock prices.
He then uses the new Microsoft 365 data types and a tiny bit of VBA code to build a minute-by-minute database of stock prices.
Table of Contents
(0:00:00) Welcome
(0:00:47) Adding Up/Down Arrows
(0:03:06) Using Data Types to pull current stock price
(0:06:16) VBA to update every minute
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2429. Minute-by-minute stock price arrows.
Hey, welcome back to MrExcel netcast. I am Bill Jelen.
This question on one of my YouTube videos from Broadway Gypsy.
Great - is there a possibility to create a minute to minute changing up down stock arrow?
Wow, that's intriguing. Yeah, I bet there is.
But you know, this question actually brings up a lot more questions.
Like do you already have the stock data or do we need to get it? Do you want just one arrow or the whole day of arrows, or just the last five arrows? Is the most recent data at the top or the bottom?
Are you using Windows, Mac, Excel Online or something else?
Do you have Microsoft 365, because that would let us use data types.
Alright, so let's start easy.
Assume that you already have the data, and then later I'll show you how to go get the data, provided you have Microsoft 365.
So what I have here today is Microsoft stock Price starting just after the market opened until about two or three minutes ago.
And to get the “Change”, we take the most recent price minus the previous price.
But the problem with that is that we're going to be getting a whole bunch of different size numbers.
I just want to know: “Are we up or down?” So the awesome way to change that is to use the SIGN function. It only returns three possible values.
If it's positive you get a one. If it's negative you get a negative one.
And if it's zero you get a 0. So only three possible answers now in that column.
Then to get the arrows, use Conditional Formatting. Icon Sets.
You could use this one, I suppose with the up, right and down arrow.
I like this one with the three triangles. It's not three triangles at all.
Hey, Microsoft it is 2 triangles and a rectangle!
But I'm going to get rid of that thing in the middle anyway, so we'll go with this one.
What we get is a little arrow indicating whether we're down or up. Now I'm going to customize this.
Conditional Formatting, Manage Rules, Edit Rule. And then here see it's based on 67% and 33%.
I'm going to change both of those to an absolute number.
There's only three possible numbers I am going to get: one, zero, or negative one.
So if we are greater than zero, that's the “1”, we use the green triangle.
No cell icon here.
If we're greater than or equal to zero, that can only be “0”. And then everything else is negative one.
And I don't want to show the minus ones and ones. I just want to show the arrow.
So choose “Show Icon Only”.
Click OK, click OK, and now we have a series of arrows showing up and down. Scroll down through.
What does the blank mean?
That means that in that minute there was no change since the last time I pulled it. The most recent change is down.
Now, this makes me think of all kinds of things.
Couldn't we be showing whether we're up or down for the whole day? Hey, this is open source.
Download the workbook, feel free to change it yourself.
So this assumes you already have the data.
But let's assume you don't have the data right now.
This requires Microsoft 365 because I'm going to use an awesome thing here called Data Types. So I'll type a stock stock symbol: MSFT.
Data, and declare that as Stocks.
It goes out to the Internet and figures out what I'm trying to talk about here. And I want the one on the NASDAQ market.
It’s funny, they didn't ask that earlier today when I set this up.
Then over here in B1, I'm going to say equal A1 dot price.
Now you should click through to this and see what the delay is.
I believe the delay is about 5 minutes.
But it depends on your local Stock Exchange though, so always check so you know what you're dealing with there.
Then, what I need is a way that every minute I will grab new data and save it.
Now there's an awesome thing that changed recently here under Data Type Refresh Settings.
They will now let me say that stocks should be refreshed automatically every five minutes.
It used to be it would only refresh with a manual Refresh, but now you can set it for every five minutes.
And, had the question been, “Can you give me an arrow every five minutes?” We would be home free.
But that wasn’t the question.
You wanted “Can we update every minute” so that forces me over the VBA.
Which means that this isn't going to work on Excel Online.
And it may not work on a Mac. If you haven't Mac, your experience may vary.
I don't have a Mac. I can't test the code.
Let's switch over here.
Alright, so here's the little sheet that I created this morning about 9:00 o'clock and I started it to run at 9:33.
What it does is every minute it grabs the current price.
Here it does a refresh, gets the current time and the current prices.
So like right there, it just updated. Did you see it?
284.65 and if I Scroll down here we see that at this last minute at 13:59 - 284.65.
If I keep talking for a minute you would see that it would update again.
Alright, now what is this in cell A1.
I converted to a data type and then it looks like here in B2 I have =A1.Price. So I know that A1 and B2 are important.
Originally I had the NOW() function there but I actually have that updating with VBA.
You'll notice that this is saved as an XLSM and please don't skip this step.
If you've never used macros before. Your file is currently in XLSX.
And XLSX, the default Excel filetype will delete your macro!
So all of the work that you do from here on out will be lost, right?
So File, Save As. Change the file type to XLSM.
If you don't have the Developer tab, right click, Customize the Ribbon and then choose Developer.
Once you have the Developer tab go in to Macro Security and move it down to the second option.
If it's already at the top, move it down to the second option.
I'm not going to recommend that you go to three or four.
If you're already there, then someone different than me suggested that and you can just leave it where it is, but it has to be at least at the second one.
Alright, and then what we're going to do is we're going to say.
Oh there, so you just updated again.
We're going to say that we're going to have a little macro.
That macro is going to copy the current price to the next row.
It's going to copy the current date and time to the next row.
And then it's going to build this formula out here, the SIGN(B6-B5), right?
So let's go take a look at that code. Tiny, tiny little bit of code.
I wasn't sure if I'd be switching to other Excel workbooks throughout the day.
This is actually running in its own instance of Excel.
By the way, a hot tip: if you need Excel to open a new instance, hold the alt key.
Just keep holding Alt. Right-click on Excel down in your taskbar.
Keep holding the alt key while you open a Excel.
It will eventually say, “Are you trying to open a new instance of Excel?” And you say “Yes, yes I am”.
Alright, so we're going to define the WS as ThisWorkbook.Worksheets(1) because it's the first worksheet.
And then here this awesome little bit of code: .RefreshLinkedDataType. WS.Cells(1, 1) is row 1, column 1 or cell A1.
That's where I have that MSFT data type.
DoEvents lets the refresh finish because it takes a little bit time to run out to the Internet and get it.
And then here in cell row two column one that's…, that’s… what is that? .
That’s cell A2. The value is Now.
Now is just the like =NOW() function in Excel, except it's not a formula, it just timestamps what time it is. And then I go and figure out what the next row is.
Now, hey, for anyone who's never done macros before, I guarantee that you're going to get this one wrong. That's an XL. Everyone thinks it's X one.
And I agree that the font makes it looks like a one.
It's an L. XL up dot Row.
So we go down to the last row in the worksheet, press the End key, press the Up Arrow key, or Control up arrow.
See what row we are on and we know that we want to go one row below that. And then I write a column one of that row.
The value from row two, column one. And the value from row two, column two.
So that's the Date and the current stock price.
And then in column three I have a little formula here. This is an R1C1 reference.
I'm not going to take the time to explain that today.
It's just beautiful that it says we're taking the value of this row and the row one above us.
And then, because I'm about to have this thing just start to run itself right?
And you know, I'm always afraid you always hear about robots that take over the world right?
I hate when Excel starts to run itself because when I try and close Excel it reopens itself right?
So, just because I've been burned by that so many times.
And I realize I realize how ridiculous that sounds! I just like to have some control.
So out here in cell, I1.
If I write the value True there, then it's going to stop. It's going to stop running automatically.
Otherwise application on time now plus one minute from now I'll run this macro again called Save price.
And then from here I did alt+F8, chose Save Price and had it start to run.
And so you see that every minute it automatically updates until I type True. So once we get past the end of the market day.
The market closes at 4 in this case.
I am going to change that to true and let it go one more minute.
So that way on that last update it won't kick off the instructions to run again.
I've closed Excel and had it just reopen and start to run the macro again. So just have a little bit of control there.
Now some hassles here is that this data is going to be growing throughout the day, although we can predict it's a six and a half hour market. So that is 210 rows.
I should go through and column C and format everything with arrows before we begin.
If you wanted things to appear at the top instead of the bottom, you have to push cells down.
I mean, there's plenty of ways to deal with whatever all those unanswered questions that Broadway Gypsy did not answer, but this kind of gives you the starting point.
If you have questions, if you can't figure it out, down below in the YouTube comments, let me know.
Well, hey, there's a couple of different books here, MrExcel 2021 Unmasking Excel deals with how to use the data types.
But to be able to automate and run every minute, that's the Excel VBA and Macros book. There's a 2019 edition now.
There's actually an edition of this going all the way back to 2003, 2007, 2010, 2013, 2016, 2019, and soon a new one for Microsoft 365.
So that will get you up the learning curve on VBA. Or, you know, you could just watch YouTube.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Well hey, I want to thank Broadway Gypsy for leaving that great question on my YouTube channel. And I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel netcast. I am Bill Jelen.
This question on one of my YouTube videos from Broadway Gypsy.
Great - is there a possibility to create a minute to minute changing up down stock arrow?
Wow, that's intriguing. Yeah, I bet there is.
But you know, this question actually brings up a lot more questions.
Like do you already have the stock data or do we need to get it? Do you want just one arrow or the whole day of arrows, or just the last five arrows? Is the most recent data at the top or the bottom?
Are you using Windows, Mac, Excel Online or something else?
Do you have Microsoft 365, because that would let us use data types.
Alright, so let's start easy.
Assume that you already have the data, and then later I'll show you how to go get the data, provided you have Microsoft 365.
So what I have here today is Microsoft stock Price starting just after the market opened until about two or three minutes ago.
And to get the “Change”, we take the most recent price minus the previous price.
But the problem with that is that we're going to be getting a whole bunch of different size numbers.
I just want to know: “Are we up or down?” So the awesome way to change that is to use the SIGN function. It only returns three possible values.
If it's positive you get a one. If it's negative you get a negative one.
And if it's zero you get a 0. So only three possible answers now in that column.
Then to get the arrows, use Conditional Formatting. Icon Sets.
You could use this one, I suppose with the up, right and down arrow.
I like this one with the three triangles. It's not three triangles at all.
Hey, Microsoft it is 2 triangles and a rectangle!
But I'm going to get rid of that thing in the middle anyway, so we'll go with this one.
What we get is a little arrow indicating whether we're down or up. Now I'm going to customize this.
Conditional Formatting, Manage Rules, Edit Rule. And then here see it's based on 67% and 33%.
I'm going to change both of those to an absolute number.
There's only three possible numbers I am going to get: one, zero, or negative one.
So if we are greater than zero, that's the “1”, we use the green triangle.
No cell icon here.
If we're greater than or equal to zero, that can only be “0”. And then everything else is negative one.
And I don't want to show the minus ones and ones. I just want to show the arrow.
So choose “Show Icon Only”.
Click OK, click OK, and now we have a series of arrows showing up and down. Scroll down through.
What does the blank mean?
That means that in that minute there was no change since the last time I pulled it. The most recent change is down.
Now, this makes me think of all kinds of things.
Couldn't we be showing whether we're up or down for the whole day? Hey, this is open source.
Download the workbook, feel free to change it yourself.
So this assumes you already have the data.
But let's assume you don't have the data right now.
This requires Microsoft 365 because I'm going to use an awesome thing here called Data Types. So I'll type a stock stock symbol: MSFT.
Data, and declare that as Stocks.
It goes out to the Internet and figures out what I'm trying to talk about here. And I want the one on the NASDAQ market.
It’s funny, they didn't ask that earlier today when I set this up.
Then over here in B1, I'm going to say equal A1 dot price.
Now you should click through to this and see what the delay is.
I believe the delay is about 5 minutes.
But it depends on your local Stock Exchange though, so always check so you know what you're dealing with there.
Then, what I need is a way that every minute I will grab new data and save it.
Now there's an awesome thing that changed recently here under Data Type Refresh Settings.
They will now let me say that stocks should be refreshed automatically every five minutes.
It used to be it would only refresh with a manual Refresh, but now you can set it for every five minutes.
And, had the question been, “Can you give me an arrow every five minutes?” We would be home free.
But that wasn’t the question.
You wanted “Can we update every minute” so that forces me over the VBA.
Which means that this isn't going to work on Excel Online.
And it may not work on a Mac. If you haven't Mac, your experience may vary.
I don't have a Mac. I can't test the code.
Let's switch over here.
Alright, so here's the little sheet that I created this morning about 9:00 o'clock and I started it to run at 9:33.
What it does is every minute it grabs the current price.
Here it does a refresh, gets the current time and the current prices.
So like right there, it just updated. Did you see it?
284.65 and if I Scroll down here we see that at this last minute at 13:59 - 284.65.
If I keep talking for a minute you would see that it would update again.
Alright, now what is this in cell A1.
I converted to a data type and then it looks like here in B2 I have =A1.Price. So I know that A1 and B2 are important.
Originally I had the NOW() function there but I actually have that updating with VBA.
You'll notice that this is saved as an XLSM and please don't skip this step.
If you've never used macros before. Your file is currently in XLSX.
And XLSX, the default Excel filetype will delete your macro!
So all of the work that you do from here on out will be lost, right?
So File, Save As. Change the file type to XLSM.
If you don't have the Developer tab, right click, Customize the Ribbon and then choose Developer.
Once you have the Developer tab go in to Macro Security and move it down to the second option.
If it's already at the top, move it down to the second option.
I'm not going to recommend that you go to three or four.
If you're already there, then someone different than me suggested that and you can just leave it where it is, but it has to be at least at the second one.
Alright, and then what we're going to do is we're going to say.
Oh there, so you just updated again.
We're going to say that we're going to have a little macro.
That macro is going to copy the current price to the next row.
It's going to copy the current date and time to the next row.
And then it's going to build this formula out here, the SIGN(B6-B5), right?
So let's go take a look at that code. Tiny, tiny little bit of code.
I wasn't sure if I'd be switching to other Excel workbooks throughout the day.
This is actually running in its own instance of Excel.
By the way, a hot tip: if you need Excel to open a new instance, hold the alt key.
Just keep holding Alt. Right-click on Excel down in your taskbar.
Keep holding the alt key while you open a Excel.
It will eventually say, “Are you trying to open a new instance of Excel?” And you say “Yes, yes I am”.
Alright, so we're going to define the WS as ThisWorkbook.Worksheets(1) because it's the first worksheet.
And then here this awesome little bit of code: .RefreshLinkedDataType. WS.Cells(1, 1) is row 1, column 1 or cell A1.
That's where I have that MSFT data type.
DoEvents lets the refresh finish because it takes a little bit time to run out to the Internet and get it.
And then here in cell row two column one that's…, that’s… what is that? .
That’s cell A2. The value is Now.
Now is just the like =NOW() function in Excel, except it's not a formula, it just timestamps what time it is. And then I go and figure out what the next row is.
Now, hey, for anyone who's never done macros before, I guarantee that you're going to get this one wrong. That's an XL. Everyone thinks it's X one.
And I agree that the font makes it looks like a one.
It's an L. XL up dot Row.
So we go down to the last row in the worksheet, press the End key, press the Up Arrow key, or Control up arrow.
See what row we are on and we know that we want to go one row below that. And then I write a column one of that row.
The value from row two, column one. And the value from row two, column two.
So that's the Date and the current stock price.
And then in column three I have a little formula here. This is an R1C1 reference.
I'm not going to take the time to explain that today.
It's just beautiful that it says we're taking the value of this row and the row one above us.
And then, because I'm about to have this thing just start to run itself right?
And you know, I'm always afraid you always hear about robots that take over the world right?
I hate when Excel starts to run itself because when I try and close Excel it reopens itself right?
So, just because I've been burned by that so many times.
And I realize I realize how ridiculous that sounds! I just like to have some control.
So out here in cell, I1.
If I write the value True there, then it's going to stop. It's going to stop running automatically.
Otherwise application on time now plus one minute from now I'll run this macro again called Save price.
And then from here I did alt+F8, chose Save Price and had it start to run.
And so you see that every minute it automatically updates until I type True. So once we get past the end of the market day.
The market closes at 4 in this case.
I am going to change that to true and let it go one more minute.
So that way on that last update it won't kick off the instructions to run again.
I've closed Excel and had it just reopen and start to run the macro again. So just have a little bit of control there.
Now some hassles here is that this data is going to be growing throughout the day, although we can predict it's a six and a half hour market. So that is 210 rows.
I should go through and column C and format everything with arrows before we begin.
If you wanted things to appear at the top instead of the bottom, you have to push cells down.
I mean, there's plenty of ways to deal with whatever all those unanswered questions that Broadway Gypsy did not answer, but this kind of gives you the starting point.
If you have questions, if you can't figure it out, down below in the YouTube comments, let me know.
Well, hey, there's a couple of different books here, MrExcel 2021 Unmasking Excel deals with how to use the data types.
But to be able to automate and run every minute, that's the Excel VBA and Macros book. There's a 2019 edition now.
There's actually an edition of this going all the way back to 2003, 2007, 2010, 2013, 2016, 2019, and soon a new one for Microsoft 365.
So that will get you up the learning curve on VBA. Or, you know, you could just watch YouTube.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Well hey, I want to thank Broadway Gypsy for leaving that great question on my YouTube channel. And I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
