Traded Volume VBA code

[negapo]

Hi all,
I'm trying to adapt a VBA code that uses the COM function getAllTradedVolume. I have use the above code in excel when i have a tab opened with one market and it works fine. I wanted to adapt it to a Coupon View Tab where there are lots of markets in the same spreadsheet. I can see that the code loops from Cell.(5,1) until it gets to a empty cell (Loop Until selecName = ""). I have tried to put something like: Loop Until selecName = "Stop" and put in Cell.(300,1) the word Stop so it stops there but i still just shows for the first market. Can some Guru help me out? My ultimate objective is just to have the weighted average odd next to every selection.

This is the code:

Code: Select all

If ba Is Nothing Then
            Set ba = New BettingAssistantCom.ComClass
        End If
        Dim tradedVol As Variant, tradedVols As Variant
        Dim totalTraded As Double, totalProfit As Double
        tradedVol = ba.getAllTradedVolume
        If Not IsEmpty(tradedVol) Then
            Dim i As Integer, r As Integer, selecName As String, j As Integer
            r = 5
            Do
                selecName = Cells(r, 1).Value
                If selecName <> "" Then
                    For i = 0 To UBound(tradedVol)
                        If tradedVol(i).Selection = selecName Then
                            Cells(r, 28).Value = tradedVol(i).actualBsp
                            Cells(r, 29).Value = tradedVol(i).totalBspBackMatchedAmount
                            Cells(r, 30).Value = tradedVol(i).totalBspLiabilityMatchedAmount
                            tradedVols = tradedVol(i).tradedVolumes
                            totalTraded = 0
                            totalProfit = 0
                            For j = 0 To UBound(tradedVols)
                                 If tradedVols(j).totalMatchedAmount <> 0 Then
                                    totalTraded = totalTraded + tradedVols(j).totalMatchedAmount
                                    totalProfit = totalProfit + (tradedVols(j).odds - 1) * tradedVols(j).totalMatchedAmount
                                 End If
                                 If tradedVols(j).odds = Cells(r, 15).Value Then
                                    Cells(r, 27).Value = tradedVols(j).totalMatchedAmount
                                 End If
                            Next
                            Cells(r, 31).Value = Round((totalProfit / totalTraded) + 1, 2)
                            Exit For
                        End If
                    Next
                End If
                r = r + 1
            Loop Until selecName = ""
        End If
       
Application.EnableEvents = True

[osknows]

I haven't used the COM functions and can't access them right now; so this is only me speculating...

Possibly the it could be the function ba.getAllTradedVolume is setting up the array 'tradedVol' for only the first market? (eg the first few rows in excel)

Once you move beyond this market in your worksheet you're not finding a match to the market in the array.

[negapo]

Thanks for the reply osknows.
I don't understand much of how the functions store the values but i think it might be that, that it only stores the values for the first market. Can someone else confirm this? Thanks in advanced

[GaryRussell]

The COM calls only work with one market at a time currently so this cannot be done with the coupon view. I am working on adding multi market capability. Sorry if you wasted your time.

[negapo]

Thanks for the reply Gary.
Not at all, i have the worksheet already done when the functions are available

[negapo]

In a related question, can anyone help me out with this code please:

Code: Select all

If ba Is Nothing Then
            Set ba = New BettingAssistantCom.ComClass
        End If
        Dim tradedVol As Variant, tradedVols As Variant
        Dim totalTraded As Double, totalProfit As Double
        tradedVol = ba.getAllTradedVolume
                If Not IsEmpty(tradedVol) Then
            Dim i As Integer, r As Integer, selecName As String, j As Integer
            r = 5
            Do
                selecName = Cells(r, 1).Value
                If selecName <> "" Then
                    For i = 0 To UBound(tradedVol)
                        If tradedVol(i).Selection = selecName Then
                            tradedVols = tradedVol(i).tradedVolumes
                            totalTraded = 0
                            totalProfit = 0
                            For j = 0 To UBound(tradedVols)
                                 If tradedVols(j).totalMatchedAmount <> 0 Then
                                    totalTraded = totalTraded + tradedVols(j).totalMatchedAmount
                                    totalProfit = totalProfit + (tradedVols(j).odds - 1) * tradedVols(j).totalMatchedAmount
                                 End If
                            Next
                            Cells(r, 27).Value = Round((totalProfit / totalTraded) + 1, 2)
                            Exit For
                        End If
                    Next
                End If
                r = r + 1
            Loop Until selecName = ""
        End If
End If


If the totalTraded for a selection is 0 it will divide by zero the totalProfit and will crash. I wanted to put a condition if totalTraded = 0 then nothing else run the code (the ideal was to ignore individual zero and have the calculations for the selections that have traded volume). I just don't know how to impose the condition because totalTraded is an array of values and I'm not used to work with arrays and don't know how.
Any help would be great. Thanks in advanced.

[osknows]

Try this

Code: Select all

If ba Is Nothing Then
            Set ba = New BettingAssistantCom.ComClass
        End If
        Dim tradedVol As Variant, tradedVols As Variant
        Dim totalTraded As Double, totalProfit As Double
        tradedVol = ba.getAllTradedVolume
                If Not IsEmpty(tradedVol) Then
            Dim i As Integer, r As Integer, selecName As String, j As Integer
            r = 5
            Do
                selecName = Cells(r, 1).Value
                If selecName <> "" Then
                    For i = 0 To UBound(tradedVol)
                        If tradedVol(i).Selection = selecName Then
                            tradedVols = tradedVol(i).tradedVolumes
                            totalTraded = 0
                            totalProfit = 0
                            traded_over_profit = 0
                            For j = 0 To UBound(tradedVols)
                                 If tradedVols(j).totalMatchedAmount <> 0 Then
                                    totalTraded = totalTraded + tradedVols(j).totalMatchedAmount
                                    totalProfit = totalProfit + (tradedVols(j).odds - 1) * tradedVols(j).totalMatchedAmount
                                    traded_over_profit = Round((totalProfit / totalTraded) + 1, 2)
                                 End If
                            Next
                         
                            Cells(r, 27).Value = traded_over_profit
                            Exit For
                        End If
                    Next
                End If
                r = r + 1
            Loop Until selecName = ""
        End If
End If


If that doesn't work this definitely will

Code: Select all

If ba Is Nothing Then
            Set ba = New BettingAssistantCom.ComClass
        End If
        Dim tradedVol As Variant, tradedVols As Variant
        Dim totalTraded As Double, totalProfit As Double
        tradedVol = ba.getAllTradedVolume
        If Not IsEmpty(tradedVol) Then
            Dim i As Integer, r As Integer, selecName As String, j As Integer
            r = 5
            Do
                selecName = Cells(r, 1).Value
                If selecName <> "" Then
                    For i = 0 To UBound(tradedVol)
                        If tradedVol(i).Selection = selecName Then
                            Cells(r, 28).Value = tradedVol(i).actualBsp
                            Cells(r, 29).Value = tradedVol(i).totalBspBackMatchedAmount
                            Cells(r, 30).Value = tradedVol(i).totalBspLiabilityMatchedAmount
                            tradedVols = tradedVol(i).tradedVolumes
                            totalTraded = 0
                            totalProfit = 0
                            For j = 0 To UBound(tradedVols)
                                 If tradedVols(j).totalMatchedAmount <> 0 Then
                                    totalTraded = totalTraded + tradedVols(j).totalMatchedAmount
                                    totalProfit = totalProfit + (tradedVols(j).odds - 1) * tradedVols(j).totalMatchedAmount
                                 End If
                                 If tradedVols(j).odds = Cells(r, 15).Value Then
                                    Cells(r, 27).Value = tradedVols(j).totalMatchedAmount
                                 End If
                            Next
                            If totalTraded <> 0 Then
                            Cells(r, 31).Value = Round((totalProfit / totalTraded) + 1, 2)
                            Else
                            Cells(r, 31).Value = 0
                            End If
                            Exit For
                        End If
                    Next
                End If
                r = r + 1
            Loop Until selecName = ""
        End If
       
Application.EnableEvents = True



[negapo]

osknows

Thank you for your help.
It definitely worked the second one with the if totaltraded <> 0.