Volume delta

[globi166]

Hi
Did anyone thought about delta in sports market trading? Is there such a thing or it exist only on future financial contracts ? By delta I mean net difference between back and lay matched volume.

[jimibt]

Hi
Did anyone thought about delta in sports market trading? Is there such a thing or it exist only on future financial contracts ? By delta I mean net difference between back and lay matched volume.

there is a concept used called VWAP (Volume Weighted Average Price). I've used this (via the api, rather than bet angel) with some level of success in pre-race trading. not sure if there's anything similar included in bet angel itself..

[ShaunWhite]

I'm trialing an approximation based on the LTP and volume change. The issue is that within the 20ms time frame there might be 19 back bets followed by a lay so the LTP appears to be a lay for a volume equivalent to all 20 trades that have happened. I can't think of another way to do it because it's not available directly.

[ShaunWhite]

I didn't take long before I found an example...my sandbox spreadsheet shows the vol change allocated to either back or lay depending on the LTP & current prices.

It runs at 20ms and each line is a 1s summary, newest at the top. During one of those secconds, in one of the 20ms packets, an 18k vol change went through and an LTP that was the same as the last know back price (hence the jump in Back Vol), but I was watching and it was definately a lay (as confirmed by the price rise). The Diff col shows the net diff between back vol and lay vol compared to the previous second.

It's not easy. That screen shot would make more sense if I could show more of it, but I'd rather not.

[ShaunWhite]

But this one was recorded as I'd expect....(hard to show exactly where it happened)
(lol you might notice my spectacular £1.60 green, just dabbling while I'm coding, that's my excuse)

Untitled.png

I'm not sure if this wild goose is worth chasing but I like to check down all the alleys even if some are blind....to completely mix my metaphores.

[globi166]

I'm not sure if this wild goose is worth chasing but I like to check down all the alleys even if some are blind....to completely mix my metaphores.

wow that is exactly what I was after. Any chance you could tell us what software (?) you using to record all this?

[ShaunWhite]

You can capture market data in Excel using Guardian and using vba you can poll that data and build a history of changes.

[RonObvious]

wow that is exactly what I was after. Any chance you could tell us what software (?) you using to record all this?

+1

For months now I've been really keen to produce a spreadsheet that can capture data in this way and a few contributors to the forum have been helpful and told me about the data capture spreadsheets on the forum but to be honest I would find it easier to learn latin at this stage than to understand how this all works!

Any chance somebody could maybe give a step by step guide (for idiots) on how to build a spreadsheet of this kind from scratch and how to use it to pull data from the markets?

I've spent hours online trying to research VBA etc but again it just makes no sense to me. Even a point to an online resource that coupld help would be really appreciated.

Great work Shaun (he says enviously)

[sa7med]

wow that is exactly what I was after. Any chance you could tell us what software (?) you using to record all this?

+1

For months now I've been really keen to produce a spreadsheet that can capture data in this way and a few contributors to the forum have been helpful and told me about the data capture spreadsheets on the forum but to be honest I would find it easier to learn latin at this stage than to understand how this all works!

Any chance somebody could maybe give a step by step guide (for idiots) on how to build a spreadsheet of this kind from scratch and how to use it to pull data from the markets?

I've spent hours online trying to research VBA etc but again it just makes no sense to me. Even a point to an online resource that coupld help would be really appreciated.

Great work Shaun (he says enviously)

If you could write me the EXACT methodology of how to do it (to consumed by other things to figure it out) I could write it up in VBA and share with you. Disclaimer: while I should be able to do it, Ive only been writing code for about 6 months now

[RonObvious]

Hi sa7med

Thanks for the offer but I guess I'm trying to learn how to do it myself from scratch, I just have no clue where to start! I want to be able to understand how a spreadsheet like this works and how to build one step by step rather than just use it and not have any clue how it actually works.

I've seen lots of videos on youtube about VBA but none of them seem to deal with this kind of thing

[sa7med]

Hi sa7med

Thanks for the offer but I guess I'm trying to learn how to do it myself from scratch, I just have no clue where to start! I want to be able to understand how a spreadsheet like this works and how to build one step by step rather than just use it and not have any clue how it actually works.

I've seen lots of videos on youtube about VBA but none of them seem to deal with this kind of thing

np, pm me if you have any specific questions, I remember how hard it was to get started but once you get over initial hurdles it becomes much easier

[ShaunWhite]

It would be a tough call to teach vba as a correspondance course. I started writing BASIC (as it was then) in 1982 so I just naturally think in code after 36 yrs at it.

It is doable (everything in life is), but expect to spend a few weeks or months rather than days or hours especially if you don't already know excel inside out.

I'm afraid I can't offer to write a load of code for people (even simple sounding things can take 100s or 1000s of lines). It takes a lot of time and unfortunately time is leisure & money. Besides if I wanted to code for people again I'd go back to the City and make 600 a day instead of a fraction of that sat here. I'm bored with coding to be honest and wouldn't do it professionally again for all the tea in China.

I'm happy to help if someone is stuck and close, but starting from scratch is just too much of a hill to climb. Maybe you could start by recording some macros just to see how actions are translated into code and how the code is structured...that's always a good start.

[ShaunWhite]

Like sa7med says, there'll be a point when it just clicks and you'll be away....keep at it.

[RonObvious]

np, pm me if you have any specific questions, I remember how hard it was to get started but once you get over initial hurdles it becomes much easier

Like sa7med says, there'll be a point when it just clicks and you'll be away....keep at it.

Thanks for the support guys, its good to know there are people who are willing to help, makes a big difference

[ShaunWhite]

I feel bad not helping......here's some sample code to get you started.

Maybe reading it will give you some idea of how you tell excel what to do. What you need to focus on is learning to read things from a sheet, doing things with it, then writing something back to a sheet.

So, you're reading stuff off the sheet and putting it in 'variables'...

...in this case taking the cell called "BVol1" from the sheet called "Bet Angel" and putting it into a variable called NewRunnerVol (Variables are like guardian signals you can put anything in)

Code: Select all

   NewRunnerVol = Sheets("Bet Angel").Range("BVol1").Value

Then you do maths and things with those variables. eg

Code: Select all

   VolChange = NewRunnerVol - CurrRunnerVol
   CurrRunnerVol = NewRunnerVol 
   

And then writing something to a sheet somewhere ..eg

Code: Select all

Sheets("Summary").Range("CurrLTA").Value = VolChange

or the same thing could be done with ..

Code: Select all

DestSheet = "Summary"
Sheets(Destsheet).Range("LTA").Value = VolChange
Sheets(Destsheet).Range("Status").Value = "Finished"

DestSheet is just a variable I've put a name into, saves a lot of risky find&repace if you decide to change a sheet name etc.

The ".value" bit tells it that you want the cell value to change...there's also things like ".Interior.ColorIndex" to change the colour.....there's tons of attributes to can change or get the value of.

..but that's lesson 2

The difficult part at first when you look at code is knowing what are viariables, what are commands or function and what is just the syntax that binds them together. If you use meaningful names for variables you'll spot them, and the rest will come with time.

Like any language I think you can manage with about 10 verbs.... don't be freaked out by the thickness of the command list manual, stick to what you need.

So, here's a big bunch of all that strung together to give you some idea of what it might look like....

I have 2 buttons on my sheet, start and stop ...one runs the macro 'StartVol' and the other runs 'StopVol' in the code below (Excel calls them macros but they're actaully 'sub proceedures' in vb land). The code wasn't for public consumption so it's a bit rough & ready and lacking in comments, it's also just a fragment so some bits are set elsewhere...long story). Don't expect to understand it...but maybe the odd bit will start to make some sense if you understood the above.

You'll see commands like "Call DisplayVol", that tells the code to jump out and run the sub proceedure (aka macro) called "DisplayVol" and then come back and carry on from where it was.


...this is me not helping btw

Code: Select all

Sub StartVol()

    Dim DestSheet As String
    DestSheet = "Volume"
    SourceSheet = "Bet Angel"
  
    Sheets(DestSheet).Range("CurrFav") = Sheets(SourceSheet).Range("BSel1")
    Sheets(DestSheet).Range("CurrBack") = Sheets(SourceSheet).Range("BBack1")
    Sheets(DestSheet).Range("CurrLay") = Sheets(SourceSheet).Range("BLay1")
    Sheets(DestSheet).Range("BackVol") = 0
    Sheets(DestSheet).Range("LayVol") = 0
    Sheets(DestSheet).Range("CurrLTP") = Sheets(SourceSheet).Range("BLTP1")
    Sheets(DestSheet).Range("CurrLTA") = 0
    Sheets(DestSheet).Range("CurrRunnerVol") = Sheets(SourceSheet).Range("BVol1")
    Sheets(DestSheet).Range("CurrLTP") = Sheets(SourceSheet).Range("BLTP1")
    
    VolDelay = Sheets(DestSheet).Range("VolDelay")
    
    DestSheet = "Preferences"
    Sheets(DestSheet).Range("RecordStatusVol") = "Yes"
      
    While Sheets(DestSheet).Range("RecordStatusVol") = "Yes" And _
          Sheets(DestSheet).Range("RecordIntVol") > 0
       DoEvents
       Call DisplayVol
       Call WaitForNextVol
    Wend
        

End Sub

Sub StopVol()

    'Set Running indicator to No
    Sheets("Preferences").Range("RecordStatusVol") = "No"
    
End Sub

Sub WaitForNextVol()
    
    Dim PauseTime As Single, Start As Single
    
    PauseTime = Sheets("Preferences").Range("RecordIntVol")    ' Set duration.
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime And _
             Sheets("Preferences").Range("RecordStatus") = "Yes" And _
             Sheets("Preferences").Range("RecordIntVol") > 0
        DoEvents    ' Yield to other processes.
    Loop
End Sub

Sub DisplayVol()
    Dim Countdown As Integer, CountdownSec As Double
    Dim VolChange As Double
    Dim CurrRunnerVol As Double, CurrLTP As Double, CurrBackPrice As Double, CurrLayPrice As Double, CurrBackVol As Double, CurrLayVol As Double
    Dim NewRunnerVol As Double, NewLTP As Double, NewBackPrice As Double, NewLayPrice As Double
    
    DestSheet = "Volume"
    SourceSheet = "Bet Angel"
    'Get existing runner volume and new
    CurrRunnerVol = Sheets(DestSheet).Range("CurrRunnerVol").Value
    NewRunnerVol = Sheets(SourceSheet).Range("BVol1").Value
    
    If Mid(Sheets(SourceSheet).Range("BCountdown1").Value, 1, 1) <> "-" Then  ' After post time
     Countdown = Sheets("SourceSheet ").Range("BCountdown1").Value * 86400 'Convert from excel time format to seconds
     
     CurrBackVol = Sheets(DestSheet).Range("BackVol").Value
     CurrLayVol = Sheets(DestSheet).Range("LayVol").Value
     CurrLTP = Sheets(DestSheet).Range("CurrLTP")
     
     If Countdown <> PrevCountdown Then
         Sheets(DestSheet).Range("VolCountdown").Value = Countdown
         VolHistory(PrevCountdown, 0) = CurrLTP
         VolHistory(PrevCountdown, 1) = CurrBackVol
         VolHistory(PrevCountdown, 2) = CurrLayVol
         
         For VolBand = 1 To 30
             Sheets(DestSheet).Range("G" & LineTxt(10 + VolBand)).Value = VolHistory(Countdown + VolBand, 0) 'ltp
             Sheets(DestSheet).Range("H" & LineTxt(10 + VolBand)).Value = VolHistory(Countdown + VolBand, 1) 'back vol
             Sheets(DestSheet).Range("I" & LineTxt(10 + VolBand)).Value = VolHistory(Countdown + VolBand, 2) 'lay vol
         Next
     
     End If
     
     VolChange = NewRunnerVol - CurrRunnerVol
     
     NewBackPrice = Sheets(SourceSheet).Range("BBack1").Value
     NewLayPrice = Sheets(SourceSheet).Range("BLay1").Value
     
     If VolChange <> 0 Then
     
        NewLTP = Sheets(SourceSheet).Range("BLTP1").Value
        NewBackPrice = Sheets(SourceSheet).Range("BBack1").Value
        
        If NewLTP = NewBackPrice Then
          Sheets(DestSheet).Range("BackVol").Value = CurrBackVol + VolChange
        Else
          Sheets(DestSheet).Range("LayVol").Value = CurrLayVol + VolChange
        End If
        
        Sheets(DestSheet).Range("CurrLTP").Value = NewLTP
        Sheets(DestSheet).Range("CurrLTA").Value = VolChange
        Sheets(DestSheet).Range("CurrRunnerVol").Value = NewRunnerVol
     
     End If
     
     CurrLTP = Sheets(DestSheet).Range("CurrLTP")
     CurrBackPrice = Sheets(DestSheet).Range("CurrBack").Value
     CurrLayPrice = Sheets(DestSheet).Range("CurrLay").Value
     
     If CurrBackPrice <> NewBackPrice Then Sheets(DestSheet).Range("CurrBack").Value = NewBackPrice
     If CurrLayPrice <> NewLayPrice Then Sheets(DestSheet).Range("CurrLay").Value = NewLayPrice
     
     PrevCountdown = Countdown
     
    End If
    
End Sub