Speedbird168l

August 28, 2023

Hope someone can help. I have invested in the developing of a trading bot which I would like to offer to IG clients as a subscriber-based service. It is a stand-alone running a script via the IG API, not a plugin to Meta Trader or downloadable application. To connect it will require clients to provide their usernames and password for API key retrieval and connection. I would imagine this will be problematic. Is there another way to onboard/connect subscribers who want to make use of the service?

November 9, 2021

@jlz got it. Will connect. Many thanks

November 9, 2021

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. 🙏

November 9, 2021

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.

November 9, 2021

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.

November 8, 2021

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

November 8, 2021

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

November 5, 2021

Does anyone ever get any joy from webapisupport@ig.com? I don't even get as much as automated reply to my queries... ☹️

November 4, 2021

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

November 4, 2021

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

November 3, 2021

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

November 3, 2021

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

November 2, 2021

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.

November 2, 2021

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?

November 2, 2021

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?

November 2, 2021

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?

November 2, 2021

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

November 2, 2021

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

November 2, 2021

@andysinclair Hello Andy. Is it possible to communicate with you regarding https://www.excelpricefeed.com/?

November 2, 2021

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?

July 12, 2021

I am seeking assistance from someone with Visual Basic experience to help me add additional functionality to my excel trading sheet. Is there anyone out there with VB coding experience who can help? Thank you.

Speedbird168l

Posts

Joined

Last visited

Content Type

Profiles

Forums

Blogs

Events

Community Tutorials

Store

Posts posted by Speedbird168l

Connecting IG subscribers to new trading bot

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Web API support

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

Excel VBSA sheet

API- Excell

Excel VBSA sheet

Tweaking my excel trader for API functionality

Markets

IG services

Trading platforms

About Us

Contact us

IG Community