Building Up Price Range History

mem_dawg · Dec 2, 2011

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

robertgama · Dec 3, 2011

I did a quick search and found this, maybe it will help:
http://excelexperts.com/VBA-Tips-Run-Code-Every-Hour-Minute-or-Second

If you do that and just have to have your conditionals set up to write the new rows of data once your conditions are met, it shouldn't be too hard.

Cheers,

Rob

robertgama · Dec 4, 2011

Do you have a live stock quotes feed that you can send me that I can try out to see if I can help you?

Cheers,

Rob.

mem_dawg · Dec 4, 2011

robertgama said:
Do you have a live stock quotes feed that you can send me that I can try out to see if I can help you?

Cheers,

Rob.

Hi Rob,

The one that I'm using has a special installation and connection to the trading station which is installed on my PC, so that one is a bit hard to set up. But I've created this excel sheet with a Data Import From Web function, it updates every minute (that's the shortest update excel does) but you can press on refresh all button on Data tab every five seconds to get it updated so that you can test it. Market is closed at the moment, opens at 6pm on Sunday NY time.

www.ivebeento.com/FF/FX_rates.xlsx

I've seen the link you sent, unfortunately I've got no VBA skills so would appreciate if you could give it a try.

cheers,
mem

robertgama · Dec 4, 2011

Is it possible for a stock to jump more than 10 points in a second? Or let's say the delta between lowest and highest is currently 8 points, is it possible for it to jump let's say 5 more points in a second?

I'm just not sure what happens to the price ranges in those situations if that is the case.

I just made my own stock price simulator to tick every second to use. Do you have some sample data so I know what is typical or even possible?

Cheers.

robertgama · Dec 4, 2011

I've created a sheet that does what you are looking for. You'll have to answer the questions above to fix a couple things up, but I think it's pretty much working.

The main question is if there can be jumps of more than 1 point per second and how to handle it.

Lets say the current price is 30,020 and the lowest and highest are currently at 30,011 & 30,020 then the price go up by 3 points. Would the price group close at 30,021 and new price group open at 30,022 with a current value of 30,023 .... OR ... would the old price group close at 30,023 (creating a larger price interval)?

Wasn't sure how to handle this as your diagram & data table always make the transition seem perfect.

Let me know and I will make the appropriate adjustment.

robertgama · Dec 4, 2011

Here is what it's looking like so far...

Here is some of the code. After it's working perfectly we can throw it out to the real code monkey's to make it more efficient. ;D

Code:

Dim runTime

Sub setRunSchedule()
    runTime = Now + TimeValue("00:00:01")
    Application.OnTime runTime, "CheckValue"
End Sub

Sub CheckValue()
    Calculate
    Range("A1").Value = runTime
    
    Call setRunSchedule
    Call simulateStockPrice
    Call testValue
End Sub

Sub simulateStockPrice()
    Dim stockPrice As Long
    Dim priceChange As Double
    Dim randValue As Double
    Randomize
    priceChange = Application.WorksheetFunction.RandBetween(-1, 1)
    stockPrice = Range("stockPrice").Value + priceChange
    Debug.Print randValue & " : " & priceChange
    If Range("openPrice").Offset(-6 + Range("currentRow").Value, 0).Value = "" Then
        Call NewPriceRange(stockPrice)
    End If
    Range("stockPrice").Value = stockPrice
    
End Sub
Sub NewPriceRange(stockPrice As Long)
    Range("openPrice").Offset(-6 + Range("currentRow").Value, 0).Value = stockPrice
    Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value = stockPrice
    Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value = stockPrice
    lowestPrice = stockPrice
    highestPrice = stockPrice
End Sub
Sub testValue()
    Dim lowestPrice As Long
    Dim highestPrice As Long
    Dim curPrice As Long
    Dim priceRange As Integer
    Dim closeTime
    closeTime = runTime
    curPrice = Range("stockPrice").Value
    lowestPrice = Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value
    highestPrice = Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value
    If curPrice < lowestPrice Then
        lowestPrice = curPrice
        Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value = lowestPrice
    ElseIf curPrice > highestPrice Then
        highestPrice = curPrice
        Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value = highestPrice
    End If
    priceRange = highestPrice - lowestPrice
    Range("openPrice").Offset(-6 + Range("currentRow").Value, 3).Value = priceRange
    If priceRange >= Range("priceInterval").Value Then
        Range("openPrice").Offset(-6 + Range("currentRow").Value, 4).Value = curPrice
        Range("openPrice").Offset(-6 + Range("currentRow").Value, 5).Value = closeTime
    End If
End Sub

mem_dawg · Dec 5, 2011

Hi Rob,

Thanks very much for sparing time for this. Is there any chance you can email me the excel file, I couldn’t apply the code you have sent, I keep on getting error messages. My email is “mntemel at gmail dot com”

About your question; yes, the price can make a jump more than 1 point at a time, let’s say the range is set to 10 points and the price suddenly went up 25 points, then the application should break that down to 10 point ranges and produce 3 lines 10 (closed) + 10 (closed) + 5 (still open) . Now the closing time for these intervals might be a problem, if we can give one second difference between them then it would be perfect! I hope it makes sense..

Cheers,
mem

robertgama · Dec 5, 2011

I emailed you the file, with instructions on how to incorporate it with your data source.

The sheet now looks like this:

I had to rewrite the code.

Code:

Dim runTime

Sub setRunSchedule()
    runTime = Now + TimeValue("00:00:01")
    Application.OnTime runTime, "CheckValue"
End Sub

Sub CheckValue()
    If UCase(Range("stop").Value) <> "Y" Then
        Calculate
        Range("A1").Value = runTime
        Call setRunSchedule
        'comment out line below when your feed is putting the stock price in B3
        Call simulateStockPrice
        Call testValue
    End If
End Sub

Sub simulateStockPrice()
    Dim stockPrice As Long
    Dim priceChange As Double

    Randomize
    priceChange = Application.WorksheetFunction.RandBetween(-4, 4)
    stockPrice = Range("stockPrice").Value + priceChange
    Range("stockPrice").Value = stockPrice
    
End Sub
Sub NewPriceRange(stockPrice As Long)
    Range("openPrice").Offset(-6 + Range("currentRow").Value, 0).Value = stockPrice
    Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value = stockPrice
    Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value = stockPrice
    lowestPrice = stockPrice
    highestPrice = stockPrice
End Sub
Sub testValue()
    Dim lowestPrice As Long
    Dim highestPrice As Long
    Dim curPrice As Long
    Dim openPrice As Long
    Dim priceRange As Integer
    Dim priceInterval As Integer
    Dim closeTime As Date
    Dim changeDirection As Integer
    Dim i As Integer

    Const millisecond As Double = (1# / (24# * 60# * 60# * 1000#))
    
    If Range("openPrice").Offset(-6 + Range("currentRow").Value, 0).Value = "" Then
        Call NewPriceRange(Range("stockPrice").Value)
    End If
    
    closeTime = runTime
    curPrice = Range("stockPrice").Value
    openPrice = Range("openPrice").Offset(-6 + Range("currentRow").Value, 0).Value
    
    'for debug to delete
    'Range("openPrice").Offset(-6 + Range("currentRow").Value, 6).Value = curPrice
    
    lowestPrice = Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value
    highestPrice = Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value
    If curPrice < lowestPrice Then
        lowestPrice = curPrice
    ElseIf curPrice > highestPrice Then
        highestPrice = curPrice
    End If
    priceRange = highestPrice - lowestPrice
    priceInterval = Range("priceInterval").Value
    
    i = 0
    If priceRange > priceInterval Then
        If curPrice = highestPrice Then
            changeDirection = 1
        ElseIf curPrice = lowestPrice Then
            changeDirection = -1
        End If
        Do
            If changeDirection = 1 Then
                highestPrice = lowestPrice + priceInterval
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value = lowestPrice
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value = highestPrice
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 3).Value = priceInterval
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 4).Value = highestPrice
                lowestPrice = highestPrice + 1
                openPrice = lowestPrice
                'MsgBox TimeValue(closeTime)
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 5).Value = CDbl(closeTime) + millisecond * 10 * i 'DateAdd("s", i, closeTime)
                'Range("openPrice").Offset(-6 + Range("currentRow").Value, 5).Value = _
                    Range("openPrice").Offset(-6 + Range("currentRow").Value, 5).Value + i * 10 * Range("millisecond").Value
                priceRange = priceRange - (priceInterval + 1)
            ElseIf changeDirection = -1 Then
                lowestPrice = highestPrice - priceInterval
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value = lowestPrice
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value = highestPrice
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 3).Value = priceInterval
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 4).Value = lowestPrice
                highestPrice = lowestPrice - 1
                openPrice = highestPrice
                Range("openPrice").Offset(-6 + Range("currentRow").Value, 5).Value = DateAdd("s", i, closeTime)
                priceRange = priceRange - (priceInterval + 1)
            End If
            Range("openPrice").Offset(-6 + Range("currentRow").Value, 0).Value = openPrice
            i = i + 1
        Loop Until priceRange < priceInterval
        If changeDirection = 1 Then
            Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value = openPrice
            Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value = openPrice + priceRange
        Else
            Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value = openPrice - priceRange
            Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value = openPrice
        End If
        Range("openPrice").Offset(-6 + Range("currentRow").Value, 3).Value = priceRange
    ElseIf priceRange <= priceInterval Then
        Range("openPrice").Offset(-6 + Range("currentRow").Value, 1).Value = lowestPrice
        Range("openPrice").Offset(-6 + Range("currentRow").Value, 2).Value = highestPrice
        Range("openPrice").Offset(-6 + Range("currentRow").Value, 3).Value = priceRange
    End If
End Sub

Sub ClearValues()

    Range("A6").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A6").Select
    Range("stop").Value = "n"
    
End Sub

robertgama · Dec 5, 2011

One more thing...
The following are special named ranges, make sure you don't delete these cells:

Building Up Price Range History

mem_dawg

New Member

Excel Facts

robertgama

Board Regular

robertgama

Board Regular

mem_dawg

New Member

robertgama

Board Regular

robertgama

Board Regular

robertgama

Board Regular

mem_dawg

New Member

robertgama

Board Regular

robertgama

Board Regular

Similar threads

Forum statistics

Share this page

Building Up Price Range History

New Member

Excel Facts

Board Regular

Board Regular

New Member

Board Regular

Board Regular

Board Regular

New Member

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock