Building Up Price Range History

[mem_dawg]

Hi All,

This might be a little bit over Excel but I was wondering if it's possible or not.

Some of you might have heard about Price Ranges if you are interested in stock market. What I need is to build up a Price Range History by monitoring the live data feed which is connected to my excel sheet.

There is one cell which gets updated every second and you can see the current market price that comes from the online data source.

Then I define a price range, lets say 10 points.

So after the opening value, price might go up or down, and when the difference between highest and lowest reaches 10 points it saves that information and the moment it jumps over that 10 points range a new range begins and that becomes the new opening point of the next range.

I know it sounds a bit confusing, that's why I've illustrated two pictures to explain this.

If you take a look at this chart below, you will see how the price has changed so far. So as you can see, after the opening, the price might go a few points up (new highest) and then might go down (new lowest), when the difference between highest and lowest reaches 10 points, it means that range has come to an end. What I need it to save these values. Opening Price, Highest, Lowest, Closing Price, and Closing Time. Chart is just an illustration, what I need is the table which can be seen in the second picture...

So as you can see, Cell B3 is the Current Price that comes from the data source, and gets updated every second.

Cell D3 is the Price Range that I want to define. In this case it is 10 points.

And below these; there is the table that builds up. Cell B14 is the starting price, and then the macro monitors the Current Price (B3) and starts changing the Lowest (C14) and Highest (D14), when the difference between Lowest and Highest reaches the limit 10 points (Range - E14), and price makes a break out from this range, that will be the opening of the new range. Actually it's easier to see this in the chart above.

So is it possible to create something like this? Your help and advice would be highly appreciated.

Cheers,
mem

 

[mem_dawg]

Hi Rob,

You are a legend! This is amazing, works just like I want it. Is there any chance we can run it with prices with 5 decimals (1.0125) with a range like 0.0009 points?

Cheers,
mem

 

[mem_dawg]

Hi Rob,

you know until the range is filled up, the close price stays blank, can we set it to current price until that range is closed?

cheers,
mem

 

[robertgama]

I emailed you the file and put the current price in the close price while the range is open.

I also fixed the formatting dynamically in case you deleted it as is seems like you did in that picture.

In terms of the 5 decimal place numbers. Just convert them as they come onto my sheet. In B3 enter the formula: =10000*(yourStockCellReferenceHere). The you can set the range to 9.

 

[mem_dawg]

This is just perfect! just a small feedback though:

While the application is running I'm getting this error message if I try to look at the other open excel files

Run-time error ‘1004’:

Method ‘Range’ of object ‘_Global’ failed

And when I click on debug button, it highlights this line in the code:

If UCase(Range("stop").Value) <> "Y" Then

Does this mean that unless I stop the application I cannot look at the other excel files? If so, is there any solution to that?

cheers,
mem

 

[robertgama]

Of course it has to be the active workbook.

You can try to start another instance of Excel or you can try to change all of the range(....) references to explicit workbook("nameoftheworkbook").range(....) and see if that works.

I've done enough though, you'll have to figure out any other changes on your own.

Cheers.

 

[mem_dawg]

thanks mate! I really appreciate your help. have a great day!

cheers,
mem