Traded volume at certain odds

[vanbuuts]

Hi, can anyone advise if the traded volume (money) at certain odds points can be displayed in the Excel file and if so how?

[Captain Sensible]

Yes if you use the COM interface, there's a getAllTradedVolume request that'll probably have the info you need. Don't ask me how though as setting up my sheets to use the COM functions are still on my 'to do' list

[vanbuuts]

Ok thanks, was hoping for something simple as not au fait with COM myself

[StuT]

Hi Vanbuuts,

I have used the getAllTradedVolume() COM function which is supplied in the sample worksheets section of the forum.
I copied the relevant VB code into my Worksheet_Change() and just commented out some of the values that I didn't want to return and check.
It worked well, I'm not sure if it's suitable for in-running though as it's another API call in a fast moving market.

To enable COM it's just a matter of going into Preferences and enabling COM on the COM Server tab.

[vanbuuts]

Thanks StuT I will take a look

[vanbuuts]

I think the file is used for capturing trades you have placed, what I am after is the traded volumes at certain odds - similar to when you click the ladder you will see 2.00 = £1.2k, 1.98 = £420, etc

[StuT]

If you look at the VBA is this not the section you are interested in:
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, 25).Value = tradedVols(j).totalMatchedAmount
End If
Next

That's the total matched amount for each price for each runner.

I checked these cell figures against the ladder and they were the same.

[vanbuuts]

Hi Stu, when I connected this sheet to my Gruss software no figures populated in columns Z:AB so I assumed this was traded volumes on bets I had placed.

I don;t want the total amount matched as that is already displayed in Column P. What I am after is if say I wanted to see money traded at odds of 2.00 for each horse somewhere this would display?

Do you understand what I mean?

You would have the 6 (for example horses) and in Column AA it would show the amount f money traded at (or below) a certain odds point - evens for example?

[StuT]

isn't column P the total amount match on that selection?

If tradedVols(j).odds = Cells(r, 15).Value Then
Cells(r, 25).Value = tradedVols(j).totalMatchedAmount
End If

As I read it the above snippet lines in the above loop show the total amount matched in column 25 for the odds of the last traded price, col 15.
So if you swapped the = Cells(r, 15).Value to say 2.00 it'll then show the total amount matched in column 25 for odds of 2.00

[vanbuuts]

For some reason I can not get any figures in columns Z,AA,AB - do these populate for you so I can't even test what the sheet is supposed to do for starters.

I have COM switched on, and no additional columns selected when I link the tabs to Excel??

[StuT]

The code only produces values to columns Y,Z,AA,AB and AC.

I was only interested in seeing the Total Matched Volume for the Last Traded Price so I commented the lines out that output to columns Z, AA, AB and AC.

I only kept the relevant lines where it output to Cells(r, 25), column Y.
This worked fine for me.

I didn't have any additional columns selected in Log Prices.

Try commenting out certain section or If statements and work your way from there.
Or put a message box out to show it's entered the code. You may have to kill it via Task manager after that though.

[vanbuuts]

Hi StuT - thank you - I have it populating data which is the first step - however it only seems to work on the first tab (I have all race venues on separate ones)

So I can get it to pull value matched for certain odds - however I can;t seem to get it to show value for certain odds and below. I have tried chaning the "=" to "<=" but it doesn't change.

Any idea's on that one?

[StuT]

daft question maybe but have you got the exact same vba code in each sheet (tab), can't see why it wouldn't work.

I'm also struggling to work out why <= would not work, saying that though, the array is read 1 item at a time so I'm not sure you'd be able to report correct volumes when checking <= odds as at that point you'd only have the volume for one of the matched odds (determined by array position j )

[vanbuuts]

Thank you. Yep I think the <= is not intelligent enough to do that.

In terms of the different tabs, literally no idea, I am using the same sheet, just disconnecting one tab and selecting another. First one works none of the others do

[Captain Sensible]

Are you using duplicate version of the excel sheet or the same sheet but different tabs? The coding is set under sheet1 so would only run for that sheet is what Stut is getting at.