Using VB2008 to acccess the Betfair API: A tutorial

[luckyjock]

Many thanks to Mumbles for his great tutorial, i have followed it so that i now have a working form.

However, i would like to try and save the markets information into a database. I have spent the last 3 nights reading, reading and more reading about datasets, datatables, databases, tableadapters, binding sources and i have got totally and utterly confused.

My first experience of any type of programming was 10 days ago when i first saw this thread and then thought i'd give it a go

I have followed the tutorial where it teaches you how to display the Markets information in a datagridview. I have changed my application so it shows all the columns that the 'marketdata' output provides in my datagridview.

I understand that the matchdata object holds the information i need to get to my database.

I spent days trying to use the tableadapters and the data sources within vb 2010 but tonight learnt that they are for typed datasets. Am i right in assuming that i have to create an untyped dataset that the datatable dtmarkets sits in, and therefore i am unable to connect the database so it shoes in a dataset that appears in the solution explorer?

Could anyone have a look at my code below and see where i am going wrong, or just give me a gentle push in the right direction as i am very keen to learn how to do stuff myself? My Sql connection works fine, i have tested this by putting in a breakpoint at the 'dtMarkets.Rows.Add(matchdata)' line and then checking the connection state and it is open. At that moment after i havei clicked on my SqlConnect button the textbox shows that the header is ok and the errorcode ok. The 'matchdata' object has 16 fields of data within it as it should do, but when i check the database table (tblMarkets) in my database there is no data in it?

Private Sub bSqlConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bSqlConnect.Click

        sqlComm.Connection = sqlConn        'Connection string and command string set as public in Unpack module
        sqlConn.Close()
        sqlConn.Open()

        ' Set Dataset and datatable 
        Dim dsBetfair As DataSet = New DataSet("MarketDetails")
        Dim dtMarkets As DataTable = dsBetfair.Tables.Add("Markets")

        Try
            With dtMarkets.Columns
                .Add("MarketID", GetType(System.String))
                .Add("MarketName", GetType(System.String))
                .Add("MarketType", GetType(System.String))
                .Add("MarketStatus", GetType(System.String))
                .Add("EventDate", GetType(System.DateTime))
                .Add("MenuPath", GetType(System.String))
                .Add("EventHierachy", GetType(System.String))
                .Add("BetDelay", GetType(System.String))
                .Add("ExchangeID", GetType(System.String))
                .Add("ISO3CountryCode", GetType(System.String))
                .Add("LastRefresh", GetType(System.DateTime))
                .Add("NoRunners", GetType(System.String))
                .Add("NoWinners", GetType(System.String))
                .Add("TtlAmountMatched", GetType(System.Double))
                .Add("BspMarket", GetType(System.Boolean))
                .Add("TurningInPlay", GetType(System.Boolean))
            End With

            Print("*** Data to SQL ***")
            Dim oMarketsReq As New BFUK.GetAllMarketsReq
            Dim oMarketsResp As BFUK.GetAllMarketsResp
            Dim matchdata(15) As Object
            With oMarketsReq
                .header = oHeaderUK()
                ReDim .eventTypeIds(0) : .eventTypeIds(0) = 7       'For Horse Racing
                ReDim .countries(1) : .countries(0) = "GBR" : .countries(1) = "ZAF"
                .fromDate = Today.AddDays(0)
                .toDate = Today.AddDays(1)
            End With
            oMarketsResp = BetFairUK.getAllMarkets(oMarketsReq)     'Call the UK API
            With oMarketsResp
                Checkheader(.header)
                Print("ErrorCode = " & .errorCode.ToString)
                If .errorCode = BFUK.GetAllMarketsErrorEnum.OK Then
                    Dim AllMarkets As New UnpackAllMarkets(.marketData)
                    With AllMarkets
                        For i = 0 To .marketData.Length - 1
                            With .marketData(i)
                                matchdata(0) = .marketId
                                matchdata(1) = .marketName
                                matchdata(2) = .marketType
                                matchdata(3) = .marketStatus
                                matchdata(4) = .eventDate.ToLocalTime
                                matchdata(5) = .menuPath
                                matchdata(6) = .eventHeirachy
                                matchdata(7) = .betDelay
                                matchdata(8) = .exchangeId
                                matchdata(9) = .countryCode
                                matchdata(10) = .lastRefresh
                                matchdata(11) = .noOfRunners
                                matchdata(12) = .noOfWinners
                                matchdata(13) = .totalAmountMatched
                                matchdata(14) = .bspMarket
                                matchdata(15) = .turningInPlay

                                dtMarkets.Rows.Add(matchdata)


                                Dim fillQueryString As String = _
              "SELECT marketId, marketName, marketType, marketStatus, eventDate, menuPath, eventHierachy, betDelay, exchangeId," & _
              "iso3CountryCode, lastRefresh, noOfRunners, noOfWinners, totalAmountMatched, bspMarket, turningInPlay FROM dbo.tblMarkets"
                                Dim adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(fillQueryString, sqlConn)

                                adapter.Fill(dsBetfair, "tblMarkets")
                            End With
                        Next
                    End With
                End If
            End With


        Catch ex As Exception
            MessageBox.Show("Failed to connect to data source")

            sqlConn.Close()
        End Try
    End Sub

[cfwnotlob]

reply to luckyjock

lucky


(this is not gospel as i usually work in access vba and not up to speed in vb2010) but I think the connection is not being made to the database

have you tried putting test data in the table and seeing if you can read that data from your progarm -- this would verify that the connection is ok

also you dont say what type of database it is trying to connect too

if its sql server try looking here at connection strings - http://www.sqlteam.com/article/sql-s...nection-string

[luckyjock]

lucky


(this is not gospel as i usually work in access vba and not up to speed in vb2010) but I think the connection is not being made to the database

have you tried putting test data in the table and seeing if you can read that data from your progarm -- this would verify that the connection is ok

also you dont say what type of database it is trying to connect too

if its sql server try looking here at connection strings - http://www.sqlteam.com/article/sql-s...nection-string

Thanks for replying.

I managed to get it to connect using

Public tblMarketTableAdapter As New DataBetfairDataSetTableAdapters.tblMarketsTableAdapter()

and then putting a

tblMarketTableAdapter.Insert(.........)

statement in.

Now i've got to have a go at the GetMarketPricesCompressed data and put that into a different table.

[Camper]

How can I know wich prices are lay and wich are back using CompleteMarketPricesCompressed ?

[Camper]

How can I know wich prices are lay and wich are back using CompleteMarketPricesCompressed ?

Dumb question Using .layamount and .backamount

[Camper]

I'm using CompleteMarketPricesCompressed to build a ladder.

This is part of my code:

For i = 0 To .runnerInfo.Length - 1
                                With .runnerInfo(i)
                                    If .selectionId = player1id Then

                                        TAM1 = Int(.totalAmountMatched) & " €"
                                        LPM1 = .lastPriceMatched
                                        Dim Row As DataGridViewRow
                                        Dim ColumnName As String = "Odd"

                                        For k = 0 To .prices.Length - 1
                                            With .prices(k)
                                                Dim x As Integer
                                                If .backAmount > 0 Then
                                                    Dim preco As String = CStr(.price)
                                                    BPrice1.Add(.price)
                                                    Dim backprice() As Double = BPrice1.ToArray
                                                    For m = 0 To backprice.Length - 1
                                                        x = backprice.Length - 1
                                                    Next

                                                    BP1 = backprice(x)

                                                    Row = (From Rows In DataGridView1.Rows.Cast(Of DataGridViewRow)() _
                                                              Where Rows.Cells(ColumnName).Value.ToString().ToUpper.Contains(preco)).FirstOrDefault

                                                    If .backAmount >= 1 Then
                                                        If Row IsNot Nothing Then
                                                            Row.Cells("BUA").Value = Int(.backAmount)
                                                        End If
                                                    End If
                                                    If .backAmount > 0 And .backAmount < 1 Then
                                                        If Row IsNot Nothing Then
                                                            Row.Cells("BUA").Value = .backAmount
                                                        End If
                                                    End If
                                                End If

                                                If .layAmount > 0 Then
                                                    Dim preco As String = CStr(.price)
                                                    LPrice1.Add(.price)
                                                    Dim layprices() As Double = LPrice1.ToArray
                                                    LP1 = layprices(0)
                                                    Row = (From Rows In DataGridView1.Rows.Cast(Of DataGridViewRow)() _
                                                                  Where Rows.Cells(ColumnName).Value.ToString().ToUpper.Contains(preco)).FirstOrDefault
                                                    If .layAmount >= 1 Then
                                                        If Row IsNot Nothing Then
                                                            Row.Cells("LUA").Value = Int(.layAmount)
                                                        End If
                                                    End If
                                                    If .layAmount > 0 And .layAmount < 1 Then
                                                        If Row IsNot Nothing Then
                                                            Row.Cells("LUA").Value = .layAmount
                                                        End If
                                                    End If
                                                End If
                                            End With
                                        Next
                                    End If

Sometimes it works great and sometimes a small bug appears:


This value should not be here ! I have no idea why, I tried all of kind of solutions. Needing help here

[Camper]

Anyone? I can't figure this out.

EDIT: I solved it, thanks anyway.

Small error, I only changed ToUpper.Contains to ToUpper.Equals

Regards

[luckyjock]

Trying to do something similar to step 6 - Dim AllMarkets

Further to my earlier post about trying to get data to a database, i have managed to get the Market data unpacked in Step 6 into a datatable and then into a SQL table via a tableadapter.

I did this by setting up an object

Dim matchdata(15) As Object

and then using Mumbles original code tweaked slightly

Dim AllMarkets As New UnpackAllMarkets(.marketData)
                With AllMarkets
                    For i = 0 To .marketData.Length - 1
                        With .marketData(i)
                            Print(.marketId & " " & .marketStatus & " " & .eventDate & " " & .marketName & " " & .menuPath)
                            matchdata(0) = .marketId
                            matchdata(1) = .marketName
                            matchdata(2) = .marketType
                            matchdata(3) = .marketStatus
                            matchdata(4) = .eventDate.ToLocalTime
                            matchdata(5) = .menuPath
                            matchdata(6) = .eventHeirachy
                            matchdata(7) = .betDelay
                            matchdata(8) = .exchangeId
                            matchdata(9) = .countryCode
                            matchdata(10) = .lastRefresh
                            matchdata(11) = .noOfRunners
                            matchdata(12) = .noOfWinners
                            matchdata(13) = .totalAmountMatched
                            matchdata(14) = .bspMarket
                            matchdata(15) = .turningInPlay

I am now trying to do something similar with the Unpack 2 module that uses the UnpackCompleteMarketPricesCompressed API call.

My problem is that the data is split into 3 parts. If i use this code it doesn't recognise the marketId or delay fields as they are part of the UnpackCompleteMarketPricesCompressed Class not the runnerInfoType. I have setup a object for the data but then not quite sure how to do the rest?

Dim runnerData(18) As Object
                        With oMarketPrices
                            Print(.marketId & "  " & .runnerInfo.Length & " runners")
                            'Process returned market prices here.
                            For i = 0 To .runnerInfo.Length - 1
                                With .runnerInfo(i)
                                    Print(.sortOrder & " " & .selectionId & " " & .totalAmountMatched & " " & .lastPriceMatched)
                                    runnerData(0) = .marketId
                                    runnerData(1) = .delay
                                    runnerData(2) = 
                                    runnerData(3) =
                                    runnerData(4) =
                                    runnerData(5) =
                                    runnerData(6) =

Could anyone point me in the general direction so i can try and do it? I thought i could probably put it like this but how would i incorporate the PricesType info and get that into my runnerData object?

Many thanks for any help, cheers

With oMarketPrices
                            Print(.marketId & "  " & .runnerInfo.Length & " runners")
                            runnerData(0) = .marketId
                            runnerData(1) = .delay
                            runnerData(2) = .removedRunners

                            'Process returned market prices here.
                            For i = 0 To .runnerInfo.Length - 1
                                With .runnerInfo(i)
                                    Print(.sortOrder & " " & .selectionId & " " & .totalAmountMatched & " " & .lastPriceMatched)
                                    runnerData(3) = .selectionId

[Geierkind]

Hi, in my getmarkets Sub, I dim the following Countries, with this code:

ReDim .countries(23) : .countries(0) = "ARG" : .countries(1) = "AUT" : .countries(2) = "BEL" : .countries(3) = "BRA" : .countries(4) = "BGR" : .countries(5) = "CHL" : .countries(6) = "COL" : .countries(7) = "HRV" : .countries(8) = "CYP" : .countries(9) = "CZE" : .countries(10) = "DNK" : .countries(11) = "NLD" : .countries(12) = "GBR" : .countries(13) = "EST" : .countries(14) = "FIN" : .countries(15) = "FRA" : .countries(16) = "DEU" : .countries(17) = "GRC" : .countries(18) = "HKG" : .countries(19) = "HUN" : .countries(20) = "ISL" : .countries(21) = "IRL" : .countries(22) = "ITA" : .countries(23) = "JPN"

This Code is working!

Now I change it to this, which is essentially the same thing, only larger, as you see:

ReDim .countries(40) : .countries(0) = "ARG" : .countries(1) = "AUT" : .countries(2) = "BEL" : .countries(3) = "BRA" : .countries(4) = "BGR" : .countries(5) = "CHL" : .countries(6) = "COL" : .countries(7) = "HRV" : .countries(8) = "CYP" : .countries(9) = "CZE" : .countries(10) = "DNK" : .countries(11) = "NLD" : .countries(12) = "GBR" : .countries(13) = "EST" : .countries(14) = "FIN" : .countries(15) = "FRA" : .countries(16) = "DEU" : .countries(17) = "GRC" : .countries(18) = "HKG" : .countries(19) = "HUN" : .countries(20) = "ISL" : .countries(21) = "IRL" : .countries(22) = "ITA" : .countries(23) = "JPN" : .countries(24) = "MEX" : .countries(25) = "USA" : .countries(26) = "NOR" : .countries(27) = "POL" : .countries(28) = "PRT" : .countries(29) = "ROM" : .countries(30) = "RUS" : .countries(31) = "GBR" : .countries(32) = "SRB" : .countries(33) = "SGP" : .countries(34) = "SVK" : .countries(35) = "SVN" : .countries(36) = "KOR" : .countries(37) = "ESP" : .countries(38) = "SWE" : .countries(39) = "CHE" : .countries(40) = "TUR"

Now the program just stops working, gives no error message but also recieves no markets at all.
What is happening here?

Thank you.

[BigSprout]

Geierkind,
Countries of type arrayofcountrycode is used to restrict the data you receive and is not mandatory, if you want all countries then just comment out the line.

'ReDim .countries(40) : .countries(0) = "ARG"......etc

Normally a response as you are receiving is due to no sporting activity happening during the time period you have specified - or BF may have a maximum number of countries to call at one time (should get an error for that tho)

[Geierkind]

Thanks for your great advice.

The source of my problem was, as I found out, Betfair not recognizing one of the countrycodes in the array. But your method of course, is far better.

[Camper]

I've been using multiple forms and I'm login in on each form.

How can I use multiple forms without using this wrong code?

EDIT: Solved.

[Mumbles0]

Step 34. Using Excel with VB2010

Some readers of this thread have expressed interest in using an Excel spreadsheet to display API data. There is, of course, a section in the forum devoted to this topic (VB6 and Excel). It is interesting to know that you can also drive Excel from a VB2010 project. In this step we will have a look how to do this.

It’s probably best to start a new project for this exercise. Include login facilities and a tLog textbox, also include the basic procedures (Print, CheckHeader, etc.) and add the web references (BFGlobal and BFUK) as given in the earlier tutorial steps. Provide some means of selecting your markets of interest, such as the TreeView of step 15 or a perhaps a "Today’s Card" ListBox using the idea of step 18. Once you have a "skeleton" project up and running we can proceed.

First we must add a reference to the Excel library. On the Project menu select "Add Reference". On the "COM" tab select "Microsoft Excel 11.0 Object Library", then click "OK".

This exercise is similar to what was outlined in Steps 31 and 32 where a TabControl was used to display the market data, but instead of using the TabControl, here we will use an Excel spreadsheet.

I have named the project’s startup form ExcelEx. Note the Imports statement at the very top of the form's code. This allows us to refer to classes, etc. within the Excel namespace without the Excel prefix. Add the constants and private variables as shown, also add more code to the form’s Load event handler sub:

Imports Excel
Public Class ExcelEx

  'A project using Excel to display markets

   ........
  ........
  ........

  Const MarketsFile = "C:\Betfair\Markets.xls"  'The Excel file containing the market data
  Const rMarketId = "B2"          'Cell for the marketId
  Const rRunners = "B7"           'Start of runners list

  Private Exl As Application              'A variable for the Excel object
  Private Mbook As Workbook               'A variable for the markets workbook
  Private WithEvents Msheet As Worksheet  'A variable for the market worksheet

  Private Sub ExcelEx_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    ........
    ........
    ........
 
    Exl = New Excel.Application  'Launch an instance of Excel
    With Exl
      .WindowState = XlWindowState.xlNormal
      .Visible = True           'Show it
      If My.Computer.FileSystem.FileExists(MarketsFile) Then
        Mbook = .Workbooks.Open(MarketsFile)   'Open the Excel file if it exists
      End If
    End With

  End Sub

When you run the project, the first code to execute is Sub ExcelEx_Load. Here Exl is an object which represents the Excel application. By creating this object we launch an instance of Excel. This object has many properties pertaining to the Excel application and we assign two of them here. Run the project and you will see what happens.

The MarketsFile constant defines a path to a spreadsheet file which will be opened if it exists. Initially, this file does not exist so Excel starts up "empty".

Now add the following two subs. I mentioned previously that you require some means of selecting markets. Arrange your code so that Sub DisplayMarketSheet is called with your marketId of interest.

Sub DisplayMarketSheet(ByVal MarketId As Integer)
  Dim r As Range

  Try
    With Exl
      If .Workbooks.Count = 0 Then  'First time run, there is no workbook
        .SheetsInNewWorkbook = 1
        Mbook = .Workbooks.Add        'Add a workbook with 1 worksheet
        Msheet = Mbook.Worksheets(1)   'A reference to the worksheet

      Else  'Workbook exists

        Msheet = FindSheet(MarketId)  'Look for a worksheet for this market
        If Msheet IsNot Nothing Then
          Msheet.Activate() 'Show the existing sheet for this market
          Exit Sub

        Else  'New market
          Msheet = Mbook.Worksheets.Add()  'Add a new sheet
        End If
      End If

      Dim Req As New BFUK.GetMarketReq With {.header = oHeaderUK(), .marketId = MarketId}
      Dim Resp As BFUK.GetMarketResp = BetFairUK.getMarket(Req)    'Call API (getMarket)
      With Resp
        CheckHeader(.header)
        If .errorCode = BFUK.GetMarketErrorEnum.OK Then  'Put the market data on the spreadsheet
          With .market

            r = Msheet.Range(rMarketId)   'The cell for the MarketId
            r.ColumnWidth = 12
            r.Value = MarketId                     'marketId in B2
            r.Offset(0, 1).ColumnWidth = 25
            r.Offset(0, 1).Value = .menuPath       'menuPath in C2
            r.Offset(1, 0).NumberFormat = "hh:mm"  'set a time format
            r.Offset(1, 0).Value = .marketTime.ToLocalTime     'marketTime in B3
            r.Offset(1, 1).Value = .name           'marketName in C3

            r = Msheet.Range(rRunners)    'Start of runner list
            With r.Offset(-2, 2) 'Back' column
              .HorizontalAlignment = XlHAlign.xlHAlignRight  'Align right
              .Value = "Back"   'Heading
            End With
            With r.Offset(-2, 3) 'Lay' column
              .HorizontalAlignment = XlHAlign.xlHAlignRight  'Align right
              .Value = "Lay"   'Heading
            End With

            For i = 0 To .runners.Length - 1  'Show runner list
              With .runners(i)
                r.Offset(i, 0).Value = .selectionId
                r.Offset(i, 1).Value = .name
              End With
            Next

            Msheet.Name = .name  'Tab is marketName
          End With

        Else  'Problem with API call
          Print("GetMarket error: " & .errorCode.ToString)
        End If
      End With

    End With
  Catch
  End Try

End Sub

Function FindSheet(ByVal MarketId As Integer) As Worksheet  'Returns the worksheet for the marketId
  Dim Sheet As Worksheet
  For Each Sheet In Mbook.Worksheets
    If Sheet.Range(rMarketId).Value = MarketId Then Return Sheet
  Next
End Function

To show a worksheet for a market you call sub DisplayMarketSheet. If a worksheet already exists for the market it simply activates. For a new market, a new worksheet is created and an API call to getMarket is made. The returned data is written to this new worksheet, with the runner info (selectionId and Name) appearing as a list starting at cell B7. The first call to DisplayMarketSheet creates a new workbook because none exists initially.

The code detail demonstrates the use of the Range object to assign values to the various cells on the spreadsheet. Frequent use is made of the Offset property to reference cells relative to an “anchor” cell. Of course there would be other ways to achieve a similar result.

Function FindSheet determines if a worksheet exists for a given marketId. If so, a reference to this worksheet is returned. If not, Nothing is returned. Note that the worksheets are identified by the marketId value in cell B2 (as defined by the constant rMarketId).

To understand more about the Excel classes and their members, for example Range, Worksheet, Worksheets, Workbook, Workbooks, Application, etc. it would be nice to have some documentation on the Interop.Excel namespace but, unfortunately, not much exists. Some information is given here and here, but this is very sketchy. You can go hunting for more.

However, the classes are virtually the same as the VBA classes. VBAers will have no problem with this. So I suggest that you learn a bit about VBA, particularly the objects used to access the spreadsheet. There is a great deal of information (tutorials, references, etc.) on the internet about VBA. A good run down on the important Range object is given here.

To finish this Step add the FormClosing event handler. This executes when you close the project. (Use this rather than closing Excel directly.)

Private Sub ExcelEx_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
  Try
    Mbook.Close(True)  'Close and save the workbook 
    Exl.Quit()         'Close Excel 
  Catch
  End Try
End Sub

This saves the workbook and closes Excel. The first time you do this you will be prompted to save the workbook. If you choose the file name Markets.xls and save in the C:\Betfair\ folder (as given by the MarketsFile constant) this file will be opened next time you run the project.

In the next Step we will call getMarketPricesCompressed to include best Back and Lay prices for the runners on the spreadsheet.


Note: This example was developed using Excel 2003. I’m fairly confident that it will work with other versions of Excel.

[Mumbles0]

Step 35. Using Excel with VB2010 (continued)

We now add code to update the prices for the market shown on the active Excel worksheet.

To get regular price updates we will call getMarketPricesCompressedAsync from a timer. Add a Timer control to your form and set these properties: Name = PriceTimer, Enabled = True, Interval = 2000 (for 2 sec period). Add this Tick event handler:

Private Sub PriceTimer_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles PriceTimer.Tick

  Try
    With Exl
      If .ActiveSheet IsNot Nothing Then
        Dim Req As New BFUK.GetMarketPricesCompressedReq
        Req.header = oHeaderUK()
        Req.marketId = .ActiveSheet.Range(rMarketId).Value   'The marketId on the current worksheet
        BetFairUK.getMarketPricesCompressedAsync(Req, New UserState(.ActiveSheet)) 'Call the API to get prices
      End If
    End With
  Catch
  End Try

End Sub

At regular intervals determined by PriceTimer, market prices for the marketId contained in cell B2 of the active spreadsheet are requested. In the API call we include a reference to the ActiveSheet so that we can check to see if it has changed when we get the response. The UserState class is as per Step 32.

The async response is processed with this event handler:

Private Sub BetFairUK_getMarketPricesCompressedCompleted(ByVal sender As Object, ByVal e As BFUK.getMarketPricesCompressedCompletedEventArgs) Handles BetFairUK.getMarketPricesCompressedCompleted
  Dim r As Range, i, j, selectionId As Integer

  Try
    If Not e.Cancelled Then
      With e.Result
        CheckHeader(.header)
        If .errorCode = BFUK.GetMarketPricesErrorEnum.OK Then 'Response is OK

          Dim Sheet As Worksheet = e.UserState.Param  'The active worksheet when the request was made
          If Sheet Is Exl.ActiveSheet Then  'This sheet is still active
            Dim oMarketPrices As New UnpackMarketPricesCompressed(.marketPrices)  'Unpack the prices
            With oMarketPrices

              r = Sheet.Range(rRunners)  'The start of the runners list
              i = 0
              Try
                Do  'Update runner prices
                  selectionId = Val(r.Offset(i, 0).Value)  'Next runner in the list
                  If selectionId = 0 Then Exit Do 'End of runners list

                  j = Array.FindIndex(.runnerPrices, Function(x As BFUK.RunnerPrices) x.selectionId = selectionId) 'Array index for this runner
                  If j >= 0 Then  'Prices exist for this runner

                    With .runnerPrices(j)
                      r.Offset(i, 2).Value = If(.bestPricesToBack.Length > 0, .bestPricesToBack(0).price, "")  'Update best back price
                      r.Offset(i, 3).Value = If(.bestPricesToLay.Length > 0, .bestPricesToLay(0).price, "")    'Update best lay price
                    End With

                  Else  'No prices, could be removed runner
                    r.Offset(i, 2).Value = ""
                    r.Offset(i, 3).Value = ""
                  End If
                  i += 1
                Loop

                r.Offset(-2, 1).Value = .marketStatus.ToString  'Show market status

              Catch
              End Try
            End With
          End If

        Else
          Print("GetMarketPricesCompressed error:" & .errorCode.ToString) 'If problem
        End If
      End With
    End If
  Catch ex As ApplicationException
    Print(e.Error.Message)
  End Try

End Sub

If the response from the API is OK, and ActiveSheet has not changed, the market prices are unpacked. The local Range object variable r is set to the cell which defines the start of the runner list (B7).

In the Do loop we extract the selectionId from column B of the worksheet for each runner in turn, and look up the oMarketPrices.runnerPrices array to find the index of the element for this selectionId. We use the data in this element to update the best Back and Lay price columns.

When the end of the runner list is reached (defined by an empty cell) the Do loop terminates and the current market status is updated (in cell C5). That's all.

If you click on a tab to select another market, the ActiveSheet changes, causing the prices for the newly-selected market to be updated the next time the timer ticks.


VB2010/Excel relationship

If you are developing an app with Excel (using the ideas in this example) you will find that the relationship between your VB2010 project and the Excel application is quite shaky.

The reason is because Excel runs concurrently on a different thread. If, for example, your project attempt to update cells on a worksheet while Excel is doing something else, Excel may complain, raising obscure COM exceptions, (such as "Exception from HRESULT: 0x800AC472".

Notice that I have enclosed the parts of the code which access the Excel objects in Try..Catch blocks. This is to prevent exceptions of this nature crashing the app.


These two steps have demonstrated how a VB2010 app can read from, or write to the cells of an Excel spreadsheet. I intend posting another step looking at responding to events raised by the Excel application.

[BigSprout]

This is most welcome Mumbles, I have been using the quick and dirty way of formatting & saving as a *.CSV file to get any data into excel

following with interest
cheers