PDA

View Full Version : Using VBA to import BDH from Bloomberg



Edward19434
11-03-2017, 10:17 AM
Hi guys,

I am new to here... I heard this is an amazing forum... Please give me some ideas of how to create this VBA please...

I am currently working on the assignment but I got stuck for a long time.

The task is to:

using VBA to import the closing price (BDH) from a list of equities in Bloomberg terminal. I have managed to do it manually by simply copy and paste, import data wizard. But the aim of the task is to automatically import the data :(

Please help!!

offthelip
11-03-2017, 12:39 PM
I did something similar a while ago hopefully the interface hasn't changed , this is the code I used to write the functions acorss the sheet "Download" from a named range called "Instruments" on the sheet "stocklist:
This code downloads prices between two dates


Sub writebdh()' this is the double quotes character
If True Then
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)
s22 = Cells(6, 7)
s23 = Cells(6, 8)
s24 = Cells(6, 9)
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


s4 = tt & Cells(7, 4) & tt
s5 = tt & Cells(5, 5) & tt
s6 = tt & "BarSz="
s7 = Cells(2, 9) & tt
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 If
End Sub




This is the formula that it creates:
=BDH("Z 1 Index","OPEN","30/01/00","03/09/12","","BarSz=","Dir=V","Dts=S","Sort=A", "Quote=C ", "UseDPDF=Y ")

offthelip
11-03-2017, 03:45 PM
To help you further on this, I have created this simplified spreadsheet which should get you started:

Tom Jones
11-04-2017, 01:42 AM
You should say that you posted on other forums.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1194844-please-help-asap-using-vba-to-import-bdh-from-bloomberg