how to automatically record markets in a quick list

[cjones198]

thanks for all the help Captain, it was the stupidist of errors, that big long of code you sent me, well i had at the end of it

end function

end function

This is a great learning curve for me,

[cjones198]

Just so im clear

if declare Price as public at the top of the market page it can be accessed else where in the workbook? but will it not retain its value relative to the worksheet change?

[Captain Sensible]

If Public variabes etc are too confusing just think of them as if they were written to a cell on a sheet i.e. we write a price from F5 into cell AA1, that old price is now available for us to access at anytime and change when we want to but will always be the same until changed. With a public variable we're just writing that Price to a piece of the computers memory that is always available to us. If we use other variables they're only accessable within the code routine they were written in other sub routines can't access them, most simply disappear and have to be assigned each time the code re-runs

[Captain Sensible]

Just so im clear

if declare Price as public at the top of the market page it can be accessed else where in the workbook? but will it not retain its value relative to the worksheet change?

Declare your Public variables in modules and they'll be accessable to all code. Make a new module like so

Code: Select all

Option Explicit
Public Price As Currency

Sub reset()

  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  End
End Sub

[cjones198]

any module? as long as its somewhere it will work? because that example you have given shows the reset stuff? is that important?

[Captain Sensible]

Any module is fine, but it's best to keep all the public variables in one place to make them easier to find, I just added the reset as you may have put that in a module.

[cjones198]

Thanks Captain

[cjones198]

Thanks for all your help Captain. Sheet is up and running.
Not sure what happens after last race of a quick pick list yet I assume it just doesn't do anything

[Captain Sensible]

Good to see you sorted it.

With the last race it depends how you're sending data to excel, if you're streaming data then once the last race is closed BA won't receive any more data from betfair so realistically there's nothing needed to do as -1 in Q2 won't have another market to go to and effectively stop. If you're refreshing excel at set intervals say 1 second then most of us simply change the refresh rate in Q2 to the max value of 1000 so the program doesn't waste resources or request unneccessary data.

Cell J3 holds the number of markets left and will change to L for the last market as far (as far as I remember) so where you have the line

Code: Select all

Worksheets("Sheet1").Range("Q2").Value = -1

we'd simply change that to

Code: Select all

If Worksheets("Sheet1").Range("J3").Value = "L" Then
Worksheets("Sheet1").Range("Q2").Value = 1000
else
Worksheets("Sheet1").Range("Q2").Value = -1
End If

[cjones198]

Thanks captain. Sorry to be a pain.
I see exactly what you ment about putting everything in modules.
I've just spent half hour trawling through loads of code to find a dead simple error.

So my question is
At present I have all variables declared as static in worksheet "Market"

Now if I put the code to log price changes into a module (it's quite a bit of code) would i just need to copy and paste

And where the old code was in worksheet just write Call "NewModule" ?

[Captain Sensible]

You stick sub routines in the modules and call those, you don't call the module.

So within a module you'd have lots of snippets of code all wrapped in their own sub routines , so yoour module may look something like

Code: Select all

    Option Explicit
    Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean
    Public currentMarket As String

    Sub logBalance()
    Dim r As Integer
    r = 2
    While Sheet2.Cells(r, 1) <> ""
    r = r + 1
    Wend
    Sheet2.Cells(r, 1) = currentMarket
    Sheet2.Cells(r, 2) = Worksheets("Sheet1").Range("I2").Value
    End Sub

    Sub reloadQPL()
    If Today <> Date Then
        Today = Date
        triggerQuickPickListReload = True
    End If

    If triggerQuickPickListReload Then

                    triggerQuickPickListReload = False
                    Worksheets("Sheet1").Range("Q2").Value = -3
                    triggerFirstMarketSelect = True
    Else
                    If triggerFirstMarketSelect Then
                    triggerFirstMarketSelect = False
                    Worksheets("Sheet1").Range("Q2").Value = -5
                    End If
    End If

    End Sub

Sub reset()
End
Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub

You'd then Call the su routine when required by simply putting

Code: Select all

Call reloadQPL


[cjones198]

thanks Captain i didnt know that!,

do you know of a better way to code the following

"English"
if a price moves 2 ticks then i log the information sheet 3 a cell is marked with all relevant information then offset 1 ready for next move eg.

if price(code code code) goes up 2 ticks then

sheet 3 (chart) (x, i). value = 2, (other stats blah blah blah)
x = x + 1
i = i + 1

but as is often the case, we can have spikes where the price moves, 4,5,6 10 ticks in one swoop.

so i wanna make a piece of code that allows for this in multiples of two,e.g.
if price moves say 8 ticks in one go then
sheet 3 (chart)(x,i).value = 2, (0 stats)
x = x + 1
i = i + 1
sheet 3 (chart)(x,i).value = 2, (0 stats)
x = x + 1
i = i + 1
sheet 3 (chart)(x,i).value = 2, (0 stats)
x = x + 1
i = i + 1
sheet 3 (chart)(x,i).value = 2, (stats that have made the price go out 8 ticks) for example matched amount of £10,000 or something
x = x + 1
i = i + 1

at present i have getTicks(Price, Sheets("Market").Cells(5, 15).Value) = 2 "blah blah blah"
getTicks(Price, Sheets("Market").Cells(5, 15).Value) = 4 then "blah blah blah"
so you can imagine how big this code has become, and im only allowing for a spike of 4 tick!

[Captain Sensible]

Not too sure what all your code mean to be honest but if you just want to get the ticks in multiples of two there's an excel function called FLOOR that rounds down a a number to the nearest multiple you specify.

Using FLOOR you could get the number of multiples of 2 ticks and then do whatever you need that many times. i.e, I get the ticks between 2.5 and 3 then divide by two and do something that many times

Code: Select all

Dim i As Integer
Dim x As Integer

i = WorksheetFunction.Floor(getTicks(2.5, 2.91), 2)
i = Abs(i) / 2
For x = 1 To i
MsgBox x
Next x

[cjones198]

every time a price moves 2 ticks it creates a two tick range

but on occasions it moves more such as spikes etc


e.g

2.58 : 2.62 [£2.3K ]
2.54 : 2.58 [£1K ] [£1K ] [ £4K]
2.50 : 2.54 [£2K ] [£1.7 K ] [£2K ] [ £2,5K]
2.46 : 2.50 [ £0 (SPIKE)] [£0.7K ] [£1K ]
2.42 : 2.50 [£3 K ]

so market opened AT 2.50
2.54 RECORD, 2.58 RECORD, 2.62 RECORD , 2.54 RECORD, 2.50 RECORD 2.42(as spike has happend i wish to record price 2.46 (2 ticks below 2.50) as £0 stats

[cjones198]

that did not come out correct at all lol