Excel VBSA sheet

[Speedbird168l]

Hello. I am trying to implement custom functionality into the excel VBA sample sheet where open trades close after reaching a limit or loss, and the auto-reopen (trade) again. I am using the ig.com rest API endpoint in excel VBA with "/positions/otc" Post method for trade (Creates an OTC position). When I am using this API in loop, it creates a few positions (trade) and stops giving the response. The loop then is stuck and excel hangs due to not responding.

Any suggestions? Is there any advanced version of "/positions/otc" POST API that creates positions in bulk which I can use in the loop that responds immediately?

[Speedbird168l]

Hello @jlz. You seem to be the go-to on API advice. Is it possible to have a chat?

 

[AndaIG]

Hi @Speedbird168l

You can also try email webapisupport@ig.com. Our IT team may be able to help. 

All the best 

Anda

[Speedbird168l]

Thank you @AndaIG. I've been trying to reach out but no response. Would be very grateful if someone can get in touch. 

 

[jlz]

2 hours ago, Speedbird168l said:

Hello @jlz. You seem to be the go-to on API advice. Is it possible to have a chat?

 

No problem, what do you need?

[jlz]

5 hours ago, Speedbird168l said:

Hello. I am trying to implement custom functionality into the excel VBA sample sheet where open trades close after reaching a limit or loss, and the auto-reopen (trade) again. I am using the ig.com rest API endpoint in excel VBA with "/positions/otc" Post method for trade (Creates an OTC position). When I am using this API in loop, it creates a few positions (trade) and stops giving the response. The loop then is stuck and excel hangs due to not responding.

Any suggestions? Is there any advanced version of "/positions/otc" POST API that creates positions in bulk which I can use in the loop that responds immediately?

 

You can't create positions in bulk, you are supposed to call the endpoint each time on every position.

A loop should be fine as long as you can handle all responses. Bear in mind that you are only allowed to call the endpoint a number of times per minute, if you pass that limit your positions will not be created. (the exact number was somewhere but I don't have it hand :D)

If you are able to create positions, your call to the endpoint is already working. In order to see where the problem is we would need to see the VBA code that you have written. I would bet on the way that code handles the response, bear in mind that the response is different when you have an error coming from the server.

If you have a successful response where the position has been created you get a json like:

{

   "dealReference": [positionReferenceId]

}

But if you have any error your response will be:

{

   "errorCode": error code from the list [https://labs.ig.com/rest-trading-api-reference/service-detail?id=608]

}

If you are expecting the attribute dealReference and you get an error instead it will raise an exception and will hang Excel.

 

[Speedbird168l]

26 minutes ago, jlz said:

No problem, what do you need?

Thanks @jlz. I've had someone help me configure the excel VBA sample sheet to auto-reopen baskets of trades after closing them once a prescribed limit of loss has been reached. I am using the ig.com rest API endpoint in excel VBA with "/positions/otc" Post method (creating an OTC position). However, when using this API in loop, it creates trades a few positions and then just stops. The loop then is then stuck and excel hangs due to not responding. Happy to share the file with you if you wish?

[jlz]

2 minutes ago, Speedbird168l said:

Thanks @jlz. I've had someone help me configure the excel VBA sample sheet to auto-reopen baskets of trades after closing them once a prescribed limit of loss has been reached. I am using the ig.com rest API endpoint in excel VBA with "/positions/otc" Post method (creating an OTC position). However, when using this API in loop, it creates trades a few positions and then just stops. The loop then is then stuck and excel hangs due to not responding. Happy to share the file with you if you wish?

Sure share the file, it will be easier to look at the code.

[Speedbird168l]

2 minutes ago, jlz said:

You can't create positions in bulk, you are supposed to call the endpoint each time on every position.

A loop should be fine as long as you can handle all responses. Bear in mind that you are only allowed to call the endpoint a number of times per minute, if you pass that limit your positions will not be created. (the exact number was somewhere but I don't have it hand :D)

If you are able to create positions, your call to the endpoint is already working. In order to see where the problem is we would need to see the VBA code that you have written. I would bet on the way that code handles the response, bear in mind that the response is different when you have an error coming from the server.

If you have a successful response where the position has been created you get a json like:

{

   "dealReference": [positionReferenceId]

}

But if you have any error your response will be:

{

   "errorCode": error code from the list [https://labs.ig.com/rest-trading-api-reference/service-detail?id=608]

}

If you are expecting the attribute dealReference and you get an error instead it will raise an exception and will hang Excel.

 

Wow. That's a generous and quick response. Thank you. I'm able to create a basket with a bulk of trades (initially inserting them manually) for pairing. They should auto re-open after closing when hitting the limit or loss. It's basically a trending tool which can invert the direction. It works sometimes and then it just hangs. I presume that might be because of the API call limit?  I can mail you the sheet for your comments if you are willing to look at it?

[Speedbird168l]

7 minutes ago, jlz said:

Sure share the file, it will be easier to look at the code.

I see I can't share Excvel here. Can I mail it to you?

 

[jlz]

3 minutes ago, Speedbird168l said:

Wow. That's a generous and quick response. Thank you. I'm able to create a basket with a bulk of trades (initially inserting them manually) for pairing. They should auto re-open after closing when hitting the limit or loss. It's basically a trending tool which can invert the direction. It works sometimes and then it just hangs. I presume that might be because of the API call limit?  I can mail you the sheet for your comments if you are willing to look at it?

I am not a Windows user, so I won't be able to open the Excel file. If you want to share anything send the VBA code in plain text. There are VBA developers in the forum , maybe they can help.
 

[Speedbird168l]

4 minutes ago, jlz said:

I am not a Windows user, so I won't be able to open the Excel file. If you want to share anything send the VBA code in plain text. There are VBA developers in the forum , maybe they can help.
 

Ok. Thank you very much. Will share the code.

 

[Speedbird168l]

36 minutes ago, Speedbird168l said:

Ok. Thank you very much. Will share the code.

 

@jlz I'm sorry I'm a trader and not a coder, so I asked a VBA dev to create the baskets functionality but I'm not sure which part of the code you need to see. There are a few windows so I will copy and paste. Apologies if it's wrong. Very grateful for your guidance.

Sub AddRowinOtherRow()
Application.EnableEvents = False

LoadDefault

    Dim direction As String
   If Sheets("Main").buyOptionButton.value Then
        direction = "BUY"
    Else
        direction = "SELL"
    End If
Sheets("Rules").Activate
    Dim LastRow As Long
    
     
'LastRow = 100
    Dim i As Long, Total As Long
    ' Use LastRow in loop
     Dim startrow As Integer
     ' StartRow = 5
      startrow = start_range()
     Dim Sno As Integer
    
LastRow = startrow + last_r
   For i = startrow To LastRow 'Loop start
      Sno = Sno + 1
        Range("A" & i).value = Sno
          If Range("B" & i).value = "" Then
            ' Add value in column B to total
           Range("B" & i) = Sheets("Main").Range("instrumentNameField")
          '  Sheet4.Cells(i, 7).value = Sheets("Main").range("percentageChange")
          Range(currency_r & i).value = Sheets("Main").currencyComboBox.value
           Range(epic_r & i).value = Sheets("Main").Range("epicTextField")
          Range(expiry_r & i).value = Sheets("Main").Range("expiryTextField")
          Range(size_r & i).value = Sheets("Main").Range("sizeTextField")
           ' ActiveSheet.Paste
        End If
         If Range("C" & i).value = "" Then
            Range("C" & i).value = direction
            Exit For
        End If
    Next i
Sheet4.Check_Margin
   ActiveWorkbook.Save
'Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
' Sub TradeButton_Click()
'Application.EnableEvents = False
'Dim isShowPosition As Boolean
'
'    If Not Sheet1.m_loggedIn Then
'       MsgBox "Please login first"
'       Exit Sub
'    End If
'
'    Dim epic As String
'    Dim expiry As String
'    Dim direction As String
'    Dim size As String
'    Dim crncy As String
'    Dim limit As String
'     Dim limitLevel As String
'      Dim stoplimit As String
'    Dim dealReference As String
'    Dim dealid As String
'    Dim count As Integer
'     Dim LastRow As Integer
'      Dim startrow As Integer
'       Dim LArray() As String
'       LoadDefault
'    '  StartRow = 5
'      'If Range("B6").value <> Empty Then
'     ' LastRow = Range("B5", Range("B5").End(xlDown)).rows.count
'    'LastRow = LastRow + (StartRow - 1)
'    'Else
'    'LastRow = StartRow
'   ' End If
'
'     startrow = start_range()
'    If range("B" & Val(startrow + 1)).value <> Empty Then
'      LastRow = range("B" & startrow, range("B" & startrow).End(xlDown)).rows.count
'    LastRow = LastRow + (startrow - 1)
'    Else
'    LastRow = startrow
'    End If
''LastRow = 100
'
' CopyRules startrow, LastRow
'Application.Cursor = xlWait
'
'   For i = startrow To LastRow 'Loop start
'   If range("B" & i).value <> "" And range("C" & i).value <> "" And range("G" & i).value = "" Then
'
'    epic = range(epic_r & i).value
'    expiry = range(expiry_r & i).value
'    size = range(size_r & i).value
'    crncy = range(currency_r & i).value
'
'    'limitLevel = Range("E" & i).value
'    'stoplimit = Range("F" & i).value
'    ''''' limitLevel = range("D" & i).value
'
'   direction = range("C" & i).value
'
'
'    'If i = 2 Then
''15 h
'
'   ' Exit Sub 'test exit
'    'End If
'        dealReference = restClient.createPosition(epic, expiry, direction, size, crncy, limit)
'   ' dealReference = restClient.createPositionNew(epic, expiry, direction, size, crncy, limit, stoplimit, limitLevel)
'
'    ' Wait for the deal confirmation
'
'    If dealReference <> "" Then
'
'     '  appendActivity "Deal submitted: " + dealReference
'       dealid = ""
'       count = 0
'       Do While dealid = "" And count < 3
'            Application.Wait (Now + TimeValue("0:00:01"))
'            dealid = restClient.dealConfirmation(dealReference)
'            count = count + 1
'       Loop
'       If dealid <> "" Then
'          dealid = restClient.dealConfirmation(dealReference)
'       End If
'       If dealid = "" Then
'        range(status_r & i).value = "Failed to retrieve deal confirmation"
'       Else
'        range(status_r & i).value = dealid
'
'         ' appendActivity "Deal confirmation: " + dealId
'       End If
'    If IsSucess(dealid) Then
'    LArray = Split(dealid, "-")
'     range("P" & i).value = LArray(2)
'    isShowPosition = True
'    End If
'
'
'    End If
'
'    End If
'    Next i
'    Application.EnableEvents = True
'    Application.Cursor = xlDefault
'
'    If isShowPosition = True Then
'       ' positionsButton_Click
'       get_positions
'
'    End If
'End Sub
Sub TradeButton_Click()
Dim startrow As Integer
startrow = start_range()
TradeInstrument startrow

End Sub
 Sub TradeInstrument(startrow As Integer)
Application.EnableEvents = False
    Application.ScreenUpdating = False
Dim isShowPosition As Boolean

    If Not Sheet1.m_loggedIn Then
       MsgBox "Please login first"
       Exit Sub
    End If

    Dim epic As String
    Dim expiry As String
    Dim direction As String
    Dim size As String
    Dim crncy As String
    Dim limit As String
     Dim limitLevel As String
      Dim stoplimit As String
    Dim dealReference As String
    Dim dealid As String
    Dim count As Integer
     Dim LastRow As Integer
     ' Dim startrow As Integer
       Dim LArray() As String
       LoadDefault
    '  StartRow = 5
      'If Range("B6").value <> Empty Then
     ' LastRow = Range("B5", Range("B5").End(xlDown)).rows.count
    'LastRow = LastRow + (StartRow - 1)
    'Else
    'LastRow = StartRow
   ' End If
    
    ' startrow = start_range()
    If Range("B" & Val(startrow + 1)).value <> Empty Then
      LastRow = Range("B" & startrow, Range("B" & startrow).End(xlDown)).rows.count
    LastRow = LastRow + (startrow - 1)
    Else
    LastRow = startrow
    End If
'LastRow = 100

 CopyRules startrow, LastRow
 clearDataRange startrow, LastRow 'it will clear data before trade
'Application.Cursor = xlWait
'range("G" & i).value = ""
   For i = startrow To LastRow 'Loop start
   If Range("B" & i).value <> "" And Range("C" & i).value <> "" And Range("P" & i).value = "" Then
 
    epic = Range(epic_r & i).value
    expiry = Range(expiry_r & i).value
    size = Range(size_r & i).value
    crncy = Range(currency_r & i).value
    
    'limitLevel = Range("E" & i).value
    'stoplimit = Range("F" & i).value
    ''''' limitLevel = range("D" & i).value
     
   direction = Range("C" & i).value
   
         
    'If i = 2 Then
'15 h
    
   ' Exit Sub 'test exit
    'End If
        dealReference = restClient.createPosition(epic, expiry, direction, size, crncy, limit)
   ' dealReference = restClient.createPositionNew(epic, expiry, direction, size, crncy, limit, stoplimit, limitLevel)
    
    ' Wait for the deal confirmation
    
    If dealReference <> "" Then
     
     '  appendActivity "Deal submitted: " + dealReference
       dealid = ""
       count = 0
       Do While dealid = "" And count < 3
            Application.Wait (Now + TimeValue("0:00:01"))
            dealid = restClient.dealConfirmation(dealReference)
            count = count + 1
       Loop
       If dealid <> "" Then
          dealid = restClient.dealConfirmation(dealReference)
       End If
       If dealid = "" Then
        Range(status_r & i).value = "Failed to retrieve deal confirmation"
       Else
        Range(status_r & i).value = dealid
       
         ' appendActivity "Deal confirmation: " + dealId
       End If
    If IsSucess(dealid) Then
    LArray = Split(dealid, "-")
     Range("P" & i).value = LArray(2)
    isShowPosition = True
    End If
    
    
    End If
    
    End If
    Next i
    Application.EnableEvents = True
   ' Application.Cursor = xlDefault
    
    If isShowPosition = True Then
       ' positionsButton_Click
       Application.Wait (Now + TimeValue("0:00:03"))
       get_positions
       
    End If
End Sub
'
' Positions button clicked
'
Private Sub get_positions()
    Application.EnableEvents = False
    If Not Sheet1.m_loggedIn Then
       MsgBox "Please login first"
       Exit Sub
    End If

'clearData
Dim dealid As String
Dim startrow As Integer
Dim LastRow As Integer
 Dim start_row As Integer
  Dim row As Integer
'Application.Cursor = xlWait
 startrow = start_range()
    If Range("B" & Val(startrow + 1)).value <> Empty Then
      LastRow = Range("B" & startrow, Range("B" & startrow).End(xlDown)).rows.count
    LastRow = LastRow + (startrow - 1)
    Else
    LastRow = startrow
    End If
    
      
   start_row = startrow
      row = startrow
      
      
 For i = startrow To LastRow 'Loop start
 dealid = Range("P" & startrow)
 If dealid <> "" Then
 

 
    Dim positions As Object
    Set positions = restClient.positionsById(dealid)
    
LoadDefault
    If Not positions Is Nothing Then
    
        Dim dataRange As Variant
       
        Dim column As Integer
         
          
           Dim sum_range As String
       ' row = 5
      '  row = start_range()
        'start_row = row
     
        column = 9
    
        Dim aPosition As Object
        Dim level As String
         Dim Bid As String
          Dim offer As String
           Dim PL As String
            Dim size As Integer
             Dim MarginFormula As String
      '  For Each aPosition In positions
           Dim market As Object
           Dim position As Object
           Dim epic As String
           Dim pricesAvailable As Boolean
            Dim Name As String
           column = 9
           Set market = positions.Item("market")
           Set position = positions.Item("position")
           pricesAvailable = market.Item("streamingPricesAvailable")
           
           epic = market.Item("epic")
           MarginFormula = ""
           
      'Range(epic_r & row).value = epic
    ' Range(expiry_r & row).value = "'" + market.Item("expiry")
   '  Range(size_r & row).value = position.Item("dealSize")
    Range(currency_r & row).value = position.Item("currency")
    
        
         ' Name = market.Item("instrumentName")
           Range("I" & row).formula = Sheet1.buildRTDString("market", epic, "BID")
         
           'column = column + 1
         
         Range("J" & row).formula = Sheet1.buildRTDString("market", epic, "OFFER")
          ' column = column + 1
           'Range("C" & row).value = position.Item("direction")
             size = position.Item("dealSize")
               Range("K" & row).value = position.Item("openLevel")
        ' level = Range("K" & row).value
          If position.Item("direction") = "BUY" Then
                ' range("G" & row).Formula = "=TRUNC((K" & row & "-I" & row & ")*" & size & "*" & CStr(market.Item("scalingFactor")) & "*-1)"
                 Range("R" & row).formula = "=((K" & row & "-I" & row & ")*" & size & "*" & CStr(market.Item("scalingFactor")) & "*-1)"
           Else
                'range("G" & row).Formula = "=TRUNC((K" & row & "-J" & row & ")*" & size & "*" & CStr(market.Item("scalingFactor")) & ")"
                 Range("R" & row).formula = "=((K" & row & "-J" & row & ")*" & size & "*" & CStr(market.Item("scalingFactor")) & ")"
           End If
          
           
       ' Next
       ' range("S" & row).value = range("H" & row).value
       If Sheet4.IsValid(Range("Q" & row)) Then
       Dim formula As String
       formula = "=" & "Q" & row & "*" & "R" & row
        Range("G" & row).formula = formula
         Range("H" & row).value = "GBP"
        Else
         Range("G" & row).formula = Range("R" & row).formula
         
      End If
      ' row = row + 1
'        range("C" & (start_row - 1)).value = "Total"
'       sum_range = "=SUM(G" & start_row & ":G" & row & ")"
'        range("G" & (start_row - 1)).formula = sum_range
'        Application.Wait (Now + TimeValue("0:00:01"))
'        Application.WorksheetFunction.RTD "IG.api.excel.RTD.IGApiRTDServer", "", "refresh"
        Range("D" & row, "K" & row).Calculate
'        Application.Wait (Now + TimeValue("0:00:10"))
        
    End If
   End If
   row = row + 1
   startrow = startrow + 1
   
Next

Application.EnableEvents = True
    Application.ScreenUpdating = True
    
 Range("C" & (start_row - 1)).value = "Total"
       sum_range = "=SUM(G" & start_row & ":G" & LastRow & ")"
        Range("G" & (start_row - 1)).formula = sum_range
        Application.Wait (Now + TimeValue("0:00:01"))
        Application.WorksheetFunction.RTD "IG.api.excel.RTD.IGApiRTDServer", "", "refresh"
       ' range("D" & row, "K" & row).Calculate
        Application.Wait (Now + TimeValue("0:00:10"))
 
     
'Application.Cursor = xlDefault
       Call Sheet4.CheckCloseTrade
 
End Sub
''this is close all trade
Sub Button1_Click()
Application.EnableEvents = False

  If Not Sheet1.m_loggedIn Then
       MsgBox "Please login first"
       Exit Sub
    End If

    Dim deal_id As String
      Dim epic As String
        Dim expiry As String
       Dim direction As String
       
    Dim size As String
    Dim limit As String
    Dim dealReference As String
    Dim dealid As String
    Dim count As Integer
    
     Dim LastRow As Integer
 Dim startrow As Integer
 
      'startrow = 5
      ' If Range("B6").value <> Empty Then
     ' LastRow = Range("B5", Range("B5").End(xlDown)).rows.count
    'LastRow = LastRow + (startrow - 1)
    'Else
    'LastRow = startrow
    'End If
     startrow = start_range()
    If Range("B" & Val(startrow + 1)).value <> Empty Then
      LastRow = Range("B" & startrow, Range("B" & startrow).End(xlDown)).rows.count
    LastRow = LastRow + (startrow - 1)
    Else
    LastRow = startrow
    End If
'LastRow = 100
CopyRules start_r, end_r 'test
 LoadDefault
   For i = startrow To LastRow 'Loop start
   
     epic = Range(epic_r & i).value
    expiry = Range(expiry_r & i).value
    size = Range(size_r & i).value
    crncy = Range(currency_r & i).value
     direction = Range("C" & i).value
      limit = Range("E" & i).value
   If epic <> "" And size <> "" And direction <> "" Then
     'deal_id = Range("AD" & i).value
     
    
   ' epic = Range("Z" & i).value '' Sheet4.Cells(i, 26).value
    'expiry = Range("AA" & i).value 'Sheet4.Cells(i, 27).value
   
   ' size = Range("AC" & i).value
   
 
       
   If (direction = "BUY") Then 'Change status for close if open then close ands vice versa
   direction = "SELL"
   Else
   direction = "BUY"
   End If
    
    dealReference = restClient.closePosition(epic, expiry, direction, size, limit)
    ' dealReference = restClient.closePositionNew(deal_id, epic, expiry, direction, size, limit)
    ' Wait for the deal confirmation
    
    If dealReference <> "" Then
     
     '  appendActivity "Deal submitted: " + dealReference
       dealid = ""
       count = 0
       Do While dealid = "" And count < 3
            Application.Wait (Now + TimeValue("0:00:01"))
            dealid = restClient.dealConfirmation(dealReference)
            count = count + 1
       Loop
       If dealid <> "" Then
          dealid = restClient.dealConfirmation(dealReference)
       End If
       If dealid = "" Then
        '  appendActivity "Failed to retrieve deal confirmation"
        Range(status_r & i).value = "Failed to retrieve deal confirmation"
       Else
       

        Range(status_r & i).value = dealid
        
        
'Dim position As Integer
'position = InStr(1, dealId, "SUCCESS")
If IsSucess(dealid) Then
 ' clearDataRange ("A5:AD" & i)
'Else
  ' text is not inide
   clearDataRange startrow, LastRow
End If

       
         ' appendActivity "Deal confirmation: " + dealId
       End If
    
    End If
    
    End If
    Next i
Application.EnableEvents = True
End Sub
Private Sub clearData()
  Dim start_r As Integer
   Dim end_r As Integer
  ' Dim range As String
       ' row = 5
        start_r = start_range()
        end_r = start_r + Sheet4.TextBoxSize.value
        'range = "A" & start_r & ":AD" & end_r
         
       ' CopyRules start_r, end_r 'test
Application.EnableEvents = False
    Application.ScreenUpdating = False
   ' ActiveSheet.range(range).Select
    'Selection.ClearContents 'test
    Range("I" & start_r, "K" & end_r).ClearContents
         Range("G" & start_r, "G" & end_r).ClearContents
           Range("P" & start_r, "P" & end_r).ClearContents
         '  changeDirection ' This will change direction in invert will checked
   ActiveWorkbook.Save
    'ActiveSheet.Range("D4:G4").Select
      '  Selection.ClearContents
    'ActiveSheet.Range("Z4:AD100").Select
     'Selection.ClearContents
    
    Application.ScreenUpdating = True
' ActiveSheet.range("A5").Select
    'restClient.streamingReconnect
    Application.EnableEvents = True
End Sub
Private Sub clearALLData()
  Dim start_r As Integer
   Dim end_r As Integer
  ' Dim range As String
       ' row = 5
        start_r = start_range()
        end_r = start_r + Sheet4.TextBoxSize.value
        'range = "A" & start_r & ":AD" & end_r
         
       ' CopyRules start_r, end_r 'test
Application.EnableEvents = False
    Application.ScreenUpdating = False
   ' ActiveSheet.range(range).Select
    'Selection.ClearContents 'test
    Range("A" & start_r, "T" & end_r).ClearContents
         'range("G" & start_r, "G" & end_r).ClearContents
          ' range("P" & start_r, "P" & end_r).ClearContents
         '  changeDirection ' This will change direction in invert will checked
   ActiveWorkbook.Save
    'ActiveSheet.Range("D4:G4").Select
      '  Selection.ClearContents
    'ActiveSheet.Range("Z4:AD100").Select
     'Selection.ClearContents
    
    Application.ScreenUpdating = True
' ActiveSheet.range("A5").Select
    'restClient.streamingReconnect
    Application.EnableEvents = True
End Sub
Public Sub clearDataRange(start_r As Integer, end_r As Integer)
    Application.ScreenUpdating = False
   ' ActiveSheet.range(range).Select
   ' Selection.ClearContents
     Range("I" & start_r, "K" & end_r).ClearContents
         Range("G" & start_r, "G" & end_r).ClearContents
           Range("P" & start_r, "P" & end_r).ClearContents
           
         ' changeDirection ' This will change direction in invert will checked
   ActiveWorkbook.Save
   
    Application.ScreenUpdating = True
    
   
    
 'ActiveSheet.range("A5").Select
    'restClient.streamingReconnect
    
End Sub
Private Sub ClearAll_Click()

clearALLData
End Sub

Private Sub revert_Click()
'revertData
get_positions
End Sub
'
' Positions button clicked
'
Private Sub positionsButton_Click()
   Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Not Sheet1.m_loggedIn Then
       MsgBox "Please login first"
       Exit Sub
    End If

clearData
    'clearData
    'watchlistSelector.value = ""
    
'Application.Cursor = xlWait

    Dim positions As Object
    Set positions = restClient.positions()
LoadDefault
    If Not positions Is Nothing Then
    
        Dim dataRange As Variant
        Dim row As Integer
        Dim column As Integer
          Dim start_row As Integer
          
           Dim sum_range As String
       ' row = 5
        row = start_range()
        start_row = row
        column = 9
        
        Dim aPosition As Object
        Dim level As String
         Dim Bid As String
          Dim offer As String
           Dim PL As String
            Dim size As Integer
             Dim MarginFormula As String
        For Each aPosition In positions
            
           Dim market As Object
           Dim position As Object
           
           Dim epic As String
           Dim pricesAvailable As Boolean
            Dim Name As String
           column = 9
           Set market = aPosition.Item("market")
           Set position = aPosition.Item("position")
           pricesAvailable = market.Item("streamingPricesAvailable")
           
           epic = market.Item("epic")
           MarginFormula = ""
      Range(epic_r & row).value = epic
     Range(expiry_r & row).value = "'" + market.Item("expiry")
     Range(size_r & row).value = position.Item("dealSize")
     Range(currency_r & row).value = position.Item("currency")
          Name = market.Item("instrumentName")
            Range("B" & row).value = Name
           Range("I" & row).formula = Sheet1.buildRTDString("market", epic, "BID")
           column = column + 1
         Range("J" & row).formula = Sheet1.buildRTDString("market", epic, "OFFER")
           column = column + 1
           Range("C" & row).value = position.Item("direction")
           'column = column + 1
          '   Range("AC" & row).value = position.Item("dealSize")
          size = position.Item("dealSize")
           'column = column + 1
            Range("K" & row).value = position.Item("openLevel")
         level = Range("K" & row).value
          If position.Item("direction") = "BUY" Then
         ' range("G" & row).formula = "=(K" & row & "-I" & row & ")*" & size & "*" & CStr(market.Item("scalingFactor")) & "*-1"
           Range("R" & row).formula = "=(K" & row & "-I" & row & ")*" & size & "*" & CStr(market.Item("scalingFactor")) & "*-1"
           Else
            ' range("G" & row).formula = "=(K" & row & "-J" & row & ")*" & size & "*" & CStr(market.Item("scalingFactor")) & ""
              Range("R" & row).formula = "=(K" & row & "-J" & row & ")*" & size & "*" & CStr(market.Item("scalingFactor")) & ""
           End If
           
                ' range("S" & row).value = range("H" & row).value
       If Sheet4.IsValid(Range("Q" & row)) Then
       Dim formula As String
       formula = "=" & "Q" & row & "*" & "R" & row
        Range("G" & row).formula = formula
         Range("H" & row).value = "USD"
        Else
         Range("G" & row).formula = Range("R" & row).formula
         
      End If
        Range("D" & row, "K" & row).Calculate
           row = row + 1
           
        Next
         
     Application.EnableEvents = False
    Application.ScreenUpdating = False
        'Range("D4").Formula = "=SUM(D5:D50)"
        Range("C" & (start_row - 1)).value = "Total"
       sum_range = "=SUM(G" & start_row & ":G" & row & ")"
        Range("G" & (start_row - 1)).formula = sum_range
        
      ' range("market_display_Rules").value = dataRange
        Application.Wait (Now + TimeValue("0:00:01"))
        Application.WorksheetFunction.RTD "IG.api.excel.RTD.IGApiRTDServer", "", "refresh"
      
        Application.Wait (Now + TimeValue("0:00:10"))
        
    End If
     Application.EnableEvents = True
'Application.Cursor = xlDefault
       Call Sheet4.CheckCloseTrade
 
End Sub

 

Function IsSucess(ByVal sucess_msg) As Boolean
Dim position As Integer
position = InStr(1, UCase(sucess_msg), "SUCCESS")
If position > 0 Then
  IsSucess = True
Else
  IsSucess = False
End If
End Function

Public Sub CopyRules(startrow As Integer, LastRow As Integer)
 On Error GoTo ErrorHandler
Dim range_1 As String
'startrow = startrow - 1 'Copy and Past Total or header details
range_1 = "B" & startrow & " : N" & LastRow

 Sheet4.Range(range_1).Copy Sheet5.Range(range_1)
 
 Sheet5.Range("I" & startrow, "K" & LastRow).ClearContents
 'Selection.ClearContents
   Sheet5.Range("G" & startrow, "G" & LastRow).ClearContents
   
   ActiveWorkbook.Save
    ' Selection.ClearContents
'     ActiveCell.ClearContents
'     ActiveCell.range("I" & startrow, "K" & LastRow).Select
'     ActiveCell.ClearContents
'range(range_1).Select
'    Selection.Copy
'   Sheets("History_Rules").Select
'    ActiveCell.range("A" & startrow).Select
'    ActiveSheet.Paste
'        ActiveCell.range("G" & startrow, "G" & LastRow).Select
'     ActiveCell.ClearContents
'     ActiveCell.range("I" & startrow, "K" & LastRow).Select
'     ActiveCell.ClearContents
'
'    Sheets("Rules").Select
'   Application.CutCopyMode = False
'Worksheets("Sheet4").range("A5:N15").Copy Worksheets("Sheet5").range("A" & startrow)
'Range(range_1).Copy Destination:=Sheet5.Range(range_1)
' Dim range_2 As String
'range_1 = "H" & startrow & ":H" & LastRow
'Range(range_1).Copy Destination:=Sheet5.Range(range_1)
 'Worksheets("Sheet4").Range(range_1).value Worksheets("Sheet5").Range(range_1)
'Dim range_1 As String
'range_1 = "L" & startrow & ":N" & LastRow
 'Range(range_1).Copy Destination:=Sheet5.Range(range_1)
'Range(range_1).Copy
'Sheet5.Range(range_1).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
'SkipBlanks:=False, Transpose:=False
 'Range.Copy to other worksheets
  '  Worksheets("Sheet4").Range(range_1).Copy Worksheets("Sheet5").Range(range_1)
    

ErrorHandler:
If Err.Number <> 0 Then
     Msg = "Error # " & str(Err.Number) & " was generated by " _
         & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
     MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    End If
    'Resume Next
  Exit Sub
 
End Sub
Sub revertData()
Dim range_1 As String
Dim start As Integer
start = start_range
start = start - 1 'Copy and Past Total or header details
range_1 = "A" & start & ":O15 "
 
 Sheet5.Range(range_1).Copy Destination:=Sheet4.Range(range_1)
End Sub

'Also change on function get_start_range if change in this fuction
Function start_range() As Integer
Dim last_range As Integer
LoadDefault
last_range = last_r
start_range = 5

Dim next_row As Integer
next_row = start_range + last_range + 3

If Sheet4.OpBasket2.value Then
'start_range = start_range + last_range + 3
start_range = next_row
End If
If Sheet4.OpBasket3.value Then
'start_range = start_range + (last_range * 2) + 5
start_range = next_row + last_range + 3
End If
If Sheet4.OpBasket4.value Then
'start_range = start_range + (last_range * 3) + 5
start_range = next_row + (last_range + 3) * 2

End If
'If Sheet4.OpBasket5.value Then
'start_range = next_row + (last_range + 3) * 3
'End If
'If Sheet4.OpBasket6.value Then
'start_range = next_row + (last_range + 3) * 4
'End If

End Function
Function get_start_range(OpBasket As Integer) As Integer
Dim last_range As Integer
LoadDefault
last_range = last_r
get_start_range = 5

Dim next_row As Integer
next_row = get_start_range + last_range + 3

If OpBasket = 2 Then
get_start_range = next_row
End If
If OpBasket = 3 Then
get_start_range = next_row + last_range + 3
End If
If OpBasket = 4 Then
get_start_range = next_row + (last_range + 3) * 2
End If
End Function

'Sub OptionButton9_Click()
'basket_range = "A4:O14"
'End Sub
'Sub OptionButton10_Click()
'basket_range = "A4:O14"
'End Sub
'Sub OptionButton11_Click()
'basket_range = "A4:O14"
'End Sub

'Sub OptionButton12_Click()
'basket_range = "A4:O14"
'End Sub
'Sub OptionButton13_Click()
'basket_range = "A4:O14"
'End Sub

Sub CopyBasket_Click()
Dim range_1 As String
Dim start As Integer
Dim end_r As Integer
start = start_range
end_r = start + Sheet4.TextBoxSize.value
'start = start - 1 'Copy and Past Total or header details
'range_1 = "A" & start & ":N" & end_r
CopyRules start, end_r
End Sub

Sub changeDirection(startrow As Integer)
Dim invert As Boolean
invert = Sheet4.ChkInvert.value

    Dim direction As String
  Dim LastRow As Integer
 'Dim startrow As Integer
 'startrow = start_range()
    If Range("B" & Val(startrow + 1)).value <> Empty Then
      LastRow = Range("B" & startrow, Range("B" & startrow).End(xlDown)).rows.count
    LastRow = LastRow + (startrow - 1)
    Else
    LastRow = startrow
    End If
    
 For i = startrow To LastRow
direction = Range("C" & i).value

If invert Then
 If (direction = "BUY") Then 'Change direction when invert check box checked ands vice versa
   direction = "SELL"
   Else
   direction = "BUY"
   End If
   Range("C" & i).value = direction
End If
Next i
   

End Sub

 

[Speedbird168l]

@jlz I created a quick vid for you of the concept.

[jlz]

There are a lot of commented code that does not do anything. I removed anything that does not trigger any action, so it is easier to read.

I pasted the code here with some highlighting, https://pastebin.com/zkaJMCwa
The link is password protected. In order to see the file use the password: DHAjBkgbjH

I can see in line 48 where the TradeInstrument function starts, and in line 243 where the position gets closed. But I can't see a "reopen" routine anywhere. In order to look for places where the position is being opened look for the word "createPosition".  There is only one place in the code that opens a position. Bear in mind that lines that start with '   '  are meant to be comments and do not do anything.

I was expecting a call to create a new position after the stop is hit but I can't see it. Either you have some extra code that has not been posted or it is not implemented.

[Speedbird168l]

Thank you very much @jlz. Really appreciate your generous help.

 

[jlz]

I've got some followers on this forum that keep downvoting me everywhere

They are persistent.

[Speedbird168l]

What is there to gain from downvoting or are they just keyboard trolls?

 

[jlz]

I don't know . It doesn't show who they are so I can't get mad at anyone

[Speedbird168l]

On 03/11/2021 at 15:27, jlz said:

There are a lot of commented code that does not do anything. I removed anything that does not trigger any action, so it is easier to read.

I pasted the code here with some highlighting, https://pastebin.com/zkaJMCwa
The link is password protected. In order to see the file use the password: DHAjBkgbjH

I can see in line 48 where the TradeInstrument function starts, and in line 243 where the position gets closed. But I can't see a "reopen" routine anywhere. In order to look for places where the position is being opened look for the word "createPosition".  There is only one place in the code that opens a position. Bear in mind that lines that start with '   '  are meant to be comments and do not do anything.

I was expecting a call to create a new position after the stop is hit but I can't see it. Either you have some extra code that has not been posted or it is not implemented.

Hi @jlz. I've found the code from the dev where it supposedly auto re-opens the trades after hitting the limit or loss. Can you detect anything from the code that might be inducing a crashing? Thank you for your help.

Dim currency_r As String
Dim epic_r As String
Dim expiry_r As String
Dim size_r As String
Dim status_r As String
Dim basket_range As String
Dim last_r As Integer

Sub LoadDefaultRules()
currency_r = "H"
epic_r = "L"
expiry_r = "M"
size_r = "N"
status_r = "O"
last_r = Sheet4.TextBoxSize.value
End Sub

 

Private Sub Worksheet_Calculate()
 
Call CheckCloseTrade
End Sub

'Private Sub Worksheet_Change(ByVal Target As Range)
'CheckCloseTrade
'End Sub

 

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'CheckCloseTrade
'End Sub

Public Sub CheckCloseTrade_old()
 On Error GoTo ErrorHandler
 'Application.EnableEvents = False
  If IsValid(Range("G4")) = False Then
 Exit Sub
 End If

 If IsValid(Range("E4")) Then
 If Round(Range("E4").value) = Round(Range("G4").value) Then
 Module2.Button1_Click
Exit Sub
 End If
 End If
 
  If IsValid(Range("F4")) Then
  If Round(Range("F4").value) = Round(Range("G4").value) Then
 Module2.Button1_Click
Exit Sub
 End If
 End If
 If IsValid(Range("G4")) Then
' Application.OnTime DateAdd("s", 1, Now), "Sheet4.CheckCloseTrade"
 Else
 'Application.OnTime DateAdd("s", 1, Now), "Sheet4.CheckCloseTrade", , False
 End If
 
 
 
 
 
 'Appliaction.OnTime DateAdd("s", 1, Now), "Sub_name"
'If Not Range("E4") Is Nothing And Not Range("G4") Is Nothing Then

'If Round(Range("E4").value) = Round(Range("G4").value) Then
'Range("E4").Font.ColorIndex = 5
'Module2.Button1_Click
'Exit Sub
'End If
'End If

'If Not Range("F4") Is Nothing And Not Range("G4") Is Nothing Then
'If Round(Range("F4").value) = Round(Range("G4").value) Then
'Range("E4").Font.ColorIndex = 5
'Module2.Button1_Click
'Exit Sub
'End If
'End If
'Application.EnableEvents = True
ErrorHandler:
  Exit Sub
End Sub

Public Sub CheckCloseTrade()
 'Application.Cursor = xlWait
CheckCloseTradeBasket (1)
CheckCloseTradeBasket (2)
CheckCloseTradeBasket (3)
CheckCloseTradeBasket (4)
'Application.OnTime DateAdd("s", 4, Now), "Sheet4.CheckCloseTrade"

 Dim start1 As Integer
start1 = get_start_range(1)
start1 = start1 - 1

 Dim start2 As Integer
start2 = get_start_range(2)
start2 = start1 - 2
 Dim start3 As Integer
start3 = get_start_range(3)
start3 = start3 - 1
 Dim start4 As Integer
start4 = get_start_range(1)
start4 = start4 - 1

 If IsValid(Range("G" & start1)) = False And IsValid(Range("G" & start2)) = False And IsValid(Range("G" & start3)) = False And IsValid(Range("G" & start4)) = False Then
' Application.OnTime DateAdd("s", 4, Now), "Sheet4.CheckCloseTrade", , False
 End If
' Application.Cursor = xlDefault
End Sub

Public Sub CheckCloseTradeBasket(start_r As Integer)
 On Error GoTo ErrorHandler
 'Application.EnableEvents = False
 Dim start_range As Integer
start_range = get_start_range(start_r)
start_range = start_range - 1
  If IsValid(Range("G" & start_range)) = False Then
 Exit Sub
 End If

 If IsValid(Range("E" & start_range)) Then
 If IsEqual(Range("E" & start_range).value, Range("G" & start_range).value) Then
CloseTrade (start_range + 1)
Module2.TradeButton_Click
Exit Sub
 End If
 End If
 
  If IsValid(Range("F" & start_range)) Then
  If IsEqual(Range("F" & start_range).value, Range("G" & start_range).value) Then
CloseTrade (start_range + 1)
Module2.changeDirection start_range + 1
Module2.TradeInstrument start_range + 1

Exit Sub
 End If
 End If
 
ErrorHandler:
  Exit Sub
End Sub
Public Function IsEqual(range1 As String, range2 As String) As Boolean
IsEqual = False
Dim num As Integer
Dim num2 As Integer
Dim arr() As String
arr = Split(range1, ".")
If (arr(0) <> 0) Then
num = Int(arr(0))
Else
num = Int(range1)
End If

arr = Split(range2, ".")
If (arr(0) <> 0) Then
num2 = Int(arr(0))
Else
num2 = Int(range2)
End If

'num2 = Int(range2)
'If num = num2 Then
'IsEqual = True
'End If
If num > 0 Then ' this work in case of limit compare
If num2 >= num Then
IsEqual = True
End If
Else
If num >= num2 Then 'it work in case of stop compare with p&L
IsEqual = True
End If
End If

End Function
Public Function IsValid(range1 As Range) As Boolean
IsValid = False
If Not range1 Is Nothing Then
'If Not IsEmpty(range1.value) Is Nothing And Not IsEmpty(range2.value) Is Nothing Then
If IsError(range1.value) = False Then
If Not range1.value = Empty Then
If Not range1.value = 0 Then
IsValid = True
End If
End If
End If
End If
End Function

Sub CloseTrade(start_range As Integer)
 On Error GoTo ErrorHandler
Application.EnableEvents = False

  If Not Sheet1.m_loggedIn Then
       MsgBox "Please login first"
       Exit Sub
    End If

    Dim deal_id As String
      Dim epic As String
        Dim expiry As String
       Dim direction As String
       
    Dim size As String
    Dim limit As String
    Dim dealReference As String
    Dim dealid As String
    Dim count As Integer
    
     Dim LastRow As Integer
 Dim startrow As Integer
 
 LoadDefaultRules
 
      startrow = start_range
       If Range("B" & (startrow + 1)).value <> Empty Then
      LastRow = Range("B" & startrow, Range("B" & startrow).End(xlDown)).rows.count
    LastRow = LastRow + (startrow - 1)
    Else
    LastRow = startrow
    End If
    
'LastRow = 100

   For i = startrow To LastRow 'Loop start
   
     epic = Range(epic_r & i).value
    expiry = Range(expiry_r & i).value
    size = Range(size_r & i).value
    crncy = Range(currency_r & i).value
     direction = Range("C" & i).value
      limit = Range("E" & i).value
   If epic <> "" And size <> "" And direction <> "" Then
       
   If (direction = "BUY") Then 'Change status for close if open then close ands vice versa
   direction = "SELL"
   Else
   direction = "BUY"
   End If

    dealReference = restClient.closePosition(epic, expiry, direction, size, limit)
   If dealReference <> "" Then
     
       dealid = ""
       count = 0
       Do While dealid = "" And count < 3
            Application.Wait (Now + TimeValue("0:00:01"))
            dealid = restClient.dealConfirmation(dealReference)
            count = count + 1
       Loop
       If dealid <> "" Then
          dealid = restClient.dealConfirmation(dealReference)
       End If
       If dealid = "" Then
        '  appendActivity "Failed to retrieve deal confirmation"
        Range(status_r & i).value = "Failed to retrieve deal confirmation"
       Else
       

        Range(status_r & i).value = dealid
        
        
'Dim position As Integer
'position = InStr(1, dealId, "SUCCESS")
If Module2.IsSucess(dealid) Then
'Dim str As String
Dim res As Boolean
'str = "A" & startrow & ":O" & LastRow
Module2.CopyRules startrow, LastRow

   
   
 Module2.clearDataRange startrow, LastRow
'Else
  ' text is not inide
End If

       End If
    
    End If
    
    End If
    Next i
Application.EnableEvents = True
ErrorHandler:
  Exit Sub
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
 If Not Intersect(Target, Range("D4:D300")) Is Nothing Then 'Margin
    If Target.value <> Empty Then
   'Call CheckCloseTrade
   Check_Margin
    End If
 End If
     If Not Intersect(Target, Range("Q5:Q300")) Is Nothing Then 'Margin
    If Target.value <> Empty Then
     row = Target.row
   If Sheet4.IsValid(Range("Q" & row)) Then
       Dim formusla As String
       formula = "=" & "Q" & row & "*" & "R" & row
        Range("G" & row).formula = formula
         Range("H" & row).value = "USD"
      End If
    End If
       End If
ErrorHandler:
  Exit Sub
End Sub

Sub Check_Margin()
Application.EnableEvents = False
    Dim margin As String
     Dim margintotal As String
   Dim startrow As Integer
    Dim LastRow As Integer
    Dim count As Integer
    
     startrow = start_range
       If Range("B" & (startrow + 1)).value <> Empty Then
      count = Range("B" & startrow, Range("B" & startrow).End(xlDown)).rows.count
  LastRow = count + (startrow - 1)
    Else
    count = 1
    LastRow = startrow
    End If
    
 
 
  margintotal = Range("D" & (startrow - 1)).value
  If Val(margintotal) > 0 Then
  margin = margintotal / count
   For i = startrow To LastRow 'Loop start
   Range("D" & i).value = margin
   'i = i + 1
   Next
   End If
   Application.EnableEvents = True
End Sub
Sub calculate_PL()
    Range("S" & row).value = Range("H" & row).value
       If Sheet4.IsValid(Range("Q" & row)) Then
       Dim formula As String
       formula = "=" & "Q" & row & "*" & "R" & row
        Range("G" & row).formula = formula
         Range("H" & row).value = "USD"
        Else
         Range("G" & row).formula = Range("R" & row).formula
         
      End If
End Sub

 

[Speedbird168l]

@jlz Further to the mssg above, is there perhaps a way we can connect via email to discuss grounds for collaboration?

[jlz]

Hi, I have updated my profile with my GitHub account and email address.

 

[Speedbird168l]

5 hours ago, jlz said:

Hi, I have updated my profile with my GitHub account and email address.

 

Thank you @jlz. Will be in touch.

[Speedbird168l]

9 minutes ago, Speedbird168l said:

Thank you @jlz. Will be in touch.

Sorry @jlz, don't know what I'm missing but can't find said details on your profile.  

[jlz]

3 hours ago, Speedbird168l said:

Sorry @jlz, don't know what I'm missing but can't find said details on your profile.  

There's a tab called 'About me' on every profile.  If you are on mobile  it is at the very bottom of the page aside from 'Activity'. 

[Speedbird168l]

2 hours ago, jlz said:

There's a tab called 'About me' on every profile.  If you are on mobile  it is at the very bottom of the page aside from 'Activity'. 

Thanks @jlz. I'm clearly a mug as I cannot find 'About Me' on any browser, mobile or otherwise. Man it's frustrating. My gmail address is ***********@*****.com if you wouldn't mind dropping me your email address pls. 🙏

[MongiIG]

39 minutes ago, Speedbird168l said:

Thanks @jlz. I'm clearly a mug as I cannot find 'About Me' on any browser, mobile or otherwise. Man it's frustrating. My gmail address is ***********@*****.com if you wouldn't mind dropping me your email address pls. 🙏

Hi @Speedbird168l

According to IG Community guidelines please see below:

For more information please click the following link: IG Community guidelines

 

All the best - MongiIG

[jlz]

They don't even allow you to post your email.

Ok let's try another route. I have edited the first pastebin we shared.

https://pastebin.com/zkaJMCwa

pass: DHAjBkgbjH

You can find my details there.

Edited November 9, 2021 by jlz

[Speedbird168l]

@jlz got it. Will connect. Many thanks