Search code examples
excelvbabloomberg

How to invoke bloomberg function in VBA

I'm trying to write vba code so that every time I type a number, it becomes a stock ticker after "HK Equity" is added by vba code. Then spreadsheet can return its RSI using Bloomberg function BTP. For example, when I type "1" in a cell, spreadsheet will return the RSI of stock "1 HK Equity" using Bloomberg function =BTP($D3,E$2,"RSI","TAPeriod=14","DSClose=LAST_PRICE","Per=d").

Here are my code:

Sub RSI()
Dim num As Integer
num = Sheets("sheet2").Cells(3, 2).Value

Dim numString As String
numString = CStr(num)

Dim ticker As String
ticker = numString + "HK Equity"

Dim rsiString As String
rsiString = Sheets("sheet2").Cells(4, 2).Value

Sheets("sheet2").Cells(5, 2).Value = "=BTP(" & ticker & "," & rsiString & "," & "RSI" & ", "    & "TAPeriod=14" & "," & "DSClose=LAST_PRICE" & "," & "Per=d" & ")"

End Sub

When I run the code it says Run-time Error '1004': application-defined or object-defined error. And debugger says the last command has a problem (i.e. Sheets("sheet2").Cells(5, 2).Value = "=BTP....) What's wrong with this command? THX!!

Solution

  • May you check the output of BTP formula?

    ' =BTP(1 HK Equity,rsi string,RSI, TAPeriod=14,DSClose=LAST_PRICE,Per=d)
Sheets("sheet2").Cells(5, 2).Value = "=BTP(" & ticker & "," & rsiString & "," & "RSI" & ", "    & "TAPeriod=14" & "," & "DSClose=LAST_PRICE" & "," & "Per=d" & ")"

    seems you have miss all escape character

    "=BTP(""" & ticker  & """,""" & rsiString  & """,""RSI"",""TAPeriod=14"",""DSClose=LAST_PRICE"",""Per=d"")"