VBA function to obtain market result

[Derek27]

Here's a snippet of code that may be useful to some. Call the function with strMatch equal to the market name as it appears in the Excel spreadsheet. strReportsPath must be set to point to your MarketReports folder. The function will change any / to _. It returns the name of the winning selection or *Error/File not found. I'm only interested in the name of the winner but you can get the profit/loss amounts by cycling through strIL_Elements(0 to 4).

I've only just written it so it hasn't been tested on anything other than over/under markets but it should work on any win market or can be modified to obtain all p/ls.

Code: Select all

Public Function ObtainWinner(ByVal strMatch As String) As String
'
' Finds winning selection from profit and loss logs
'

Const strReportsPath As String = "<user folder>\AppData\Roaming\Bet Angel\Bet Angel Professional\MarketReports\"

Dim byt As Byte
Dim strFileDate As String
Dim strFileName As String
Dim strFilePath As String
Dim strIL_Elements() As String
Dim strInputLine As String
Dim strWinner As String

' Remove / from market name if neccessary
    strFileName = strMatch
    byt = InStr(1, strMatch, "/")
    If byt > 0 Then Mid(strFileName, byt, 1) = "_"
    
    strFileDate = Format(Day(Now), "00") & "_" & Format(Month(Now), "00") & "_" & Year(Now)
    strFilePath = strReportsPath & strFileDate & "\ProfitAndLoss\ProfitLossReport_" & strFileDate & "_" & strFileName & ".csv"
    
' Check file exists
    If Dir(strFilePath) = "" Then
    
    ' Try yesterday's folder in case midnight's been crossed
        strFileDate = Format(Day(Now) - 1, "00") & "_" & Format(Month(Now), "00") & "_" & Year(Now)
        strFilePath = strReportsPath & strFileDate & "\ProfitAndLoss\ProfitLossReport_" & strFileDate & "_" & strFileName & ".csv"
        If Dir(strFilePath) = "" Then ObtainWinner = "*File not found"
        GoTo Exit_Function
    End If
    
    
    Open strFilePath For Input As #2
    
    Do Until EOF(2) Or strWinner <> ""
        Line Input #2, strInputLine
        strIL_Elements = Split(strInputLine, ",")
        If strIL_Elements(4) = "Winner" Then strWinner = strIL_Elements(1)
    Loop
    
    If strWinner = "" Then strWinner = "*Error"
    ObtainWinner = strWinner
    
Exit_Function:
    Close #2
    
End Function