Multiple markets at the same time using COM marketdepth?

[diceman121]

Hi Gary,
I'm using a spreadsheet that uses COM market depth to give me 10 most competitive back & lay prices and amount offered (my spreadsheet is based around one of your examples... much appreciated!). I connect the sheet to a tab in Betting Assistant and it works fine. However when I add an additional tab with a different market in the quickpick list and connect a copy of my spreadsheet, it retrns 0's for the prices returned using COM.

I'm guessing I have to tell each which market they should be looking at?...
Any ideas how I can do this...?

Thanks in advance for any suggestions.

Cheers,
Dice

[osknows]

Use ba.TabIndex to select the tab

eg

Code: Select all

ba.TabIndex = 1
result = ba.openMarket(BAEvents(.Range("AD2").Value).eventId, BAEvents(.Range("AD2").Value).exchangeId)

would use tab 1

[diceman121]

Hi Osknows
First of all thanks for the super swift response!

The TabIndex sounds perfect... but... sorry mate, vba knowedge isn't my strong point. I'm using something along the lines of the following but can't see how to apply your suggestion...

Code: Select all

Private Sub ba_getMarketDepthComplete(ByVal MarketDepth As Variant, ByVal resultCode As String)
    On Error GoTo getMarketDepthCompleteError
    Dim prices As Variant
    Dim wsCells
    If Not IsEmpty(MarketDepth) And resultCode = "OK" Then
        Dim i As Integer, r As Integer, selecName As String, j As Integer, c As Integer, idx As Integer
        Dim backCount As Integer, layCount As Integer, rowCount As Integer
        r = 5
        Do
            selecName = Cells(r, 1).Value
            r = r + 1
        Loop Until selecName = ""
        rowCount = r - 6
        ReDim wsCells(rowCount - 1, 39) As Double
        r = 5
        Do
            selecName = Cells(r, 1).Value
            If selecName <> "" Then
                For i = 0 To UBound(MarketDepth)
                    If MarketDepth(i).Selection = selecName Then
                        backCount = -1
                        layCount = -1
                        prices = MarketDepth(i).prices
                        For j = 0 To UBound(prices)
                             If prices(j).backAmountAvailable > 0 Then
                                backCount = backCount + 1
                                ReDim Preserve backPrices(backCount)
                                Set backPrices(backCount) = prices(j)
                             End If
                             If prices(j).layAmountAvailable > 0 Then
                                layCount = layCount + 1
                                ReDim Preserve layPrices(layCount)
                                Set layPrices(layCount) = prices(j)
                             End If
                        Next
                        If backCount <> -1 Then
                            QuickSort backPrices
                            For c = 1 To 10
                                idx = backCount - (c - 1)
                                If idx <= UBound(backPrices) Then
                                    wsCells(r - 5, (c - 1) * 2) = backPrices(idx).odds
                                    wsCells(r - 5, ((c - 1) * 2) + 1) = backPrices(idx).backAmountAvailable
                                Else
                                    wsCells(r - 5, (c - 1) * 2) = 0
                                    wsCells(r - 5, ((c - 1) * 2) + 1) = 0
                                End If
                            Next
                        End If
                        If layCount <> -1 Then
                            QuickSort layPrices
                            For c = 1 To 10
                                idx = c - 1
                                If idx <= UBound(layPrices) Then
                                    wsCells(r - 5, ((c - 1) * 2) + 20) = layPrices(idx).odds
                                    wsCells(r - 5, ((c - 1) * 2) + 21) = layPrices(idx).layAmountAvailable
                                Else
                                    wsCells(r - 5, ((c - 1) * 2) + 20) = 0
                                    wsCells(r - 5, ((c - 1) * 2) + 21) = 0
                                End If
                            Next
                        End If
                        Exit For
                   End If
                Next
            End If
            r = r + 1
        Loop Until selecName = ""
        Range([Z5], Cells(5 + (rowCount - 1), 65)) = wsCells
    End If
getMarketDepthCompleteError:
End Sub

thanks agian for your help

Cheers

[osknows]

I thought you were attempting something else so ba.TabIndex is not what's needed here.

I'd imagine the problem is that the code doesn't state which worksheet to lookup the names on so will use the active sheet.

For example, selecName = Cells(r, 1).Value (which sheet?)

The calling sub/function needs to pass the sheetname -

Code: Select all

Private Sub ba_getMarketDepthComplete(ByVal MarketDepth As Variant, ByVal resultCode As String, byval WS as worksheet)

then any reference to cells/range etc need to use this object, eg

Code: Select all

With WS


  selecName = .Cells(r, 1).Value
 
 
 
  .Range([Z5], Cells(5 + (rowCount - 1), 65)) = wsCells


end with

[diceman121]

Oh god... yes it was that simple! School boy!

Sorted... Thanks very much!

[diceman121]

Hi again,
Apologies, looks like I spoke too soon.

It works fine over 2 spread sheets (on 2 tabs within BA) when they're on the same market, but when I move one of the sheets to a different market (a later race for example) then the COM returns 0's for both prices (and amount offered) in the spreadsheet linked to the second tab.

Cheers
Dice

[osknows]

Are you connecting through the Excel quick link?

I've just dug out the spreadsheet and it works fine when linking a single tab using Excel/Log current prices as you've described.

However, when I try linking using Excel/Log multiple sheets quick link even 1 tab doesn't work. Possibly a bug with the COM function?

It looks like a fix is needed from Gary/Mark...

[diceman121]

Hi,
I'm linking each tab to individual excel workbooks using Excel/Log current prices...

I generally have one race meeting running in the first tab (linked to one excel work book via Excel/Log curent prices) and a second race meeting running a second tab (linked to a second excel work book via Excel/Log curent prices)...

Cheers
Dice

[osknows]

It looks like the COM function only returns data from the first sheet in Excel and returns zero because the second sheet and the returned data don't match

[alrodopial]

Can the MarketDepth return the amount available to back/lay at Sp?

Something similar to MarketDepth().prices().backAmountAvailable