BDH Formula in Macro VBA

[excelnewbie2018]

Dear community,

I would like to build a macro from directly obtaining Bloomberg Data (PX_LAST and OP_INT) for a large set of stock.

Has anybody of you already tried to insert the BDH function into a Macro? How did you o it for multiple stocks? Could you create 1 table only?

I am sorry fr this qestion but I am really stuck and I do not even know where I should start.

Thanks in advance for your precious help

 

[offthelip]

I was doing something similar a few years ago, the way I solved it was to write out the BDH functions automatically using this code

Sub writebdh()
' this is the double quotes character
tt = Chr(34)


Sheets("Stocklist").Select
inarr = Range("Instruments")
cnt = Range("Instruments").Rows.Count


Sheets("Download").Select
For i = 1 To cnt
titl = inarr(i, 1)




s1 = tt & titl & tt
s21 = Cells(6, 6) 'Open
s22 = Cells(6, 7) ' High
s23 = Cells(6, 8) ' low
s24 = Cells(6, 9) ' Last_Price
s2 = tt & s21 & tt & " " & tt & s22 & tt & " " & tt & s23 & tt & " " & tt & s24 & tt
'open price only for this test
s2 = tt & s21 & tt
s3 = tt & Cells(7, 2) & tt  ' Start date


s4 = tt & Cells(7, 4) & tt 'end date
s5 = tt & Cells(5, 5) & tt 'Bartp=T
s6 = tt & "BarSz="
s7 = Cells(2, 9) & tt  ' Period
Endstr = "," & tt & "Dir=V" & tt & "," & tt & "Dts=S" & tt & "," & tt & "Sort=A" & tt & ", " & tt & "Quote=C " & tt & ", " & tt & "UseDPDF=Y " & tt & ")"
frmtxt = "=BDH(" & s1 & "," & s2 & "," & s3 & "," & s4 & "," & s5 & "," & s6 & s7 & Endstr
Sheets("Download").Select
Cells(7, 7 + i * 7).Formula = frmtxt
Next i
End Sub