Bloomberg links and macros

[thepharcyde]

Anyone familiar with these. I have a macro that pulls BDP links from Bloomberg. Unfortunately the links only update on completion of the macro not during.

Does anyone know if this can be done, because as part of the macro it copies and pastes all the data as values so it can be looked at by anyone not just those with a BBG terminal. Pasting as values unfortunatly just outputs '#N/A Requesting Data.... Whereas if I stop the macro and run it in two stages. I.e. main macro. Then a 2nd that pastes values it works. But I want to run it in one step!

Thank you

 

[Colin Legg]

Please can you post your code and a shot of your worksheet which shows any relevant data and formulas.

I have Bloomberg so I can play around with what you have to try to come up with a solution for you?

 

[thepharcyde]

This is the underlying code.

As you can see it's in two stages, separating the paste special in Step2 and formatting. Running as one outputs blanks. In Step1 when it stops the data updates
Sub Step1()
Application.ScreenUpdating = False
Cells.Select
Range("G20").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Columns("F:F").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'Dim Val As Variant
'Val = Application.InputBox("Please enter month end date", , "i.e. 28 Feb 2009")
'Range("A4").Value = Val

Columns("H:H").Select
Selection.Insert Shift:=xlToRight

Range("H7").Select
ActiveCell.Value = "'equity"

Dim rngData As Range, rngFormula As Range

With ThisWorkbook.Worksheets("Sheet1")
Set rngData = .Range("G1:G" & .Cells(.Rows.Count, "G").End(xlUp).Row)
Set rngFormula = .Range("H7")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row, rngFormula.Column))
End With

Columns("I:I").Select
Selection.Insert Shift:=xlToRight

Range("I7").Select

ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"" "",RC[-1],"" "",""sedol1"")"
With ThisWorkbook.Worksheets("Sheet1")

Set rngData = .Range("G1:G" & .Cells(.Rows.Count, "G").End(xlUp).Row)
Set rngFormula = .Range("I7")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row, rngFormula.Column))
End With

Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Range("J6").Value = "ID_ISIN"
Range("J7").Select
ActiveCell.FormulaR1C1 = "=BDP(RC[-1],R6C10)"
With ThisWorkbook.Worksheets("Sheet1")
Set rngData = .Range("G1:G" & .Cells(.Rows.Count, "G").End(xlUp).Row)
Set rngFormula = .Range("J7")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row, rngFormula.Column))
End With

Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Range("K6").Value = "TICKER_AND_EXCH_CODE"
Range("K7").Select
ActiveCell.FormulaR1C1 = "=BDP(RC[-2],R6C11)"
With ThisWorkbook.Worksheets("Sheet1")
Set rngData = .Range("G1:G" & .Cells(.Rows.Count, "G").End(xlUp).Row)
Set rngFormula = .Range("K7")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row, rngFormula.Column))
End With
MsgBox ("Complete, please run Step2 macro")
''
''



Application.ScreenUpdating = False
End Sub

Sub Step2()


'Final format
Application.ScreenUpdating = False

Cells.Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("O:S").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("P:AO").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Range("H8").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With
Columns("A:E").Select
Selection.EntireColumn.Hidden = False
Columns("B:D").Select
Selection.Delete Shift:=xlToLeft
Range("D10").Select
'
'
'
' Columns("G:G").Select
' Selection.Copy
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Cells.Replace What:="#N/A Invalid Security", Replacement:="", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:G").Select
Columns("B:G").EntireColumn.AutoFit
Columns("G:K").Select
Range("I1").Activate
Selection.EntireColumn.Hidden = False
Columns("J").Select
Selection.Delete Shift:=xlToLeft
Columns("G:H").Select
Selection.Font.Bold = False
Range("G6:H6").Select
Selection.Font.Bold = True
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Range("E6").Select
ActiveCell.FormulaR1C1 = "ISIN"
Range("F6").Select
ActiveCell.FormulaR1C1 = "Ticker"
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").EntireColumn.AutoFit
Range("A2").Select
Cells.Replace What:="accrued income", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="total for: equities", Replacement:="", LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Total: ", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").ColumnWidth = 33
Application.ScreenUpdating = True
MsgBox ("Completed, please check a few... ")
End Sub

 

[Colin Legg]

Hi,

That code doesn't mean a lot to me - I can't see the data you're trying to pull in - but I was able to simulate your scenario independently.

I set up a worksheet like this with the securities in column B and the fields in row 1 for referencing from the BDH() worksheet function.

Excel Workbook
	B	C	D	E	F	G	H
1		PX_LAST	PX_BID	PX_ASK	PX_LOW	PX_HIGH	PX_MID
2	AA UN Equity
3	AAPL UW Equity
4	ABT UN Equity
Sheet1

Excel 2002



The problem here is that, once the bloomberg formulas have been inserted, they can't refresh until the procedure has ended. This means that if you convert the formulas to values before the procedure ends, then you will just get the "#N/A Requesting Data..." in the cells - this is what you have observed.

I added a reference to the BloombergUI library (via VBE | Tools | References) and then wrote the following code in a standard code module:

Dim xlCalc As XlCalculation
Sub Test1()
    'early bound - reference to Bloomberg
 
    'save the calculation setting and then set to automatic
    xlCalc = Application.Calculation
    Application.Calculation = xlCalculationAutomatic
 
    Sheet1.Range("C2:H4").Formula = "=BDP($B2,C$1)"
    BloombergUI.RefreshAllStaticData
    Application.OnTime Now + TimeValue("00:00:02"), "HardCode"
End Sub
 
Sub HardCode()
    Sheet1.Range("C2:H4").Value = Sheet1.Range("C2:H4").Value
    Application.Calculation = xlCalc
End Sub

This produces the desired result 95% of the time. The 2 second pause between procedures is generally sufficient to allow the cells to update.

However, this solution is not entirely satisfactory because (IMO) it's pretty flakey. The timing is not guaranteed to work. An alternative approach which avoids formulas altogether is required.

On your Bloomberg terminal if you type in WAPI< GO > you will find listings of the Bloomberg API and downloadable examples.

Using the helpfile information in that area we can build a more robust solution to this using the Bloomberg Data Type Library. Go to Tools | References and add a reference to this library. This code can then be used to populate the cells:

Sub Test2()
    Dim vResults, vSecurities, vFields
    Dim objBloomberg As BLP_DATA_CTRLLib.BlpData
 
    'fill our arrays - must be 1 dimension so we transpose from the worksheet
    With Application.WorksheetFunction
        vSecurities = .Transpose(Sheet1.Range("B2:B4").Value)
        vFields = .Transpose(.Transpose(Range("C1:H1").Value))
    End With
 
    Set objBloomberg = New BLP_DATA_CTRLLib.BlpData
    objBloomberg.AutoRelease = False
 
    objBloomberg.Subscribe _
                Security:=vSecurities, _
                cookie:=1, _
                Fields:=vFields, _
                Results:=vResults
 
    Sheet1.Range("C2:H4").Value = vResults
End Sub

Excel Workbook
	B	C	D	E	F	G	H
1		PX_LAST	PX_BID	PX_ASK	PX_LOW	PX_HIGH	PX_MID
2	AA UN Equity	12.6	12.6	12.62	12.42	12.65	12.61
3	AAPL UW Equity	172.93	172.91	172.93	172	173.13	172.92
4	ABT UN Equity	45.91	45.89	45.91	45.26	45.91	45.9
Sheet1

Excel 2002


This works 100% time for me. I haven't seen any information on whether or not we need to explicitly unsubscribe/release the BlpData object, so I will leave this for you to research. If needs be you can always ask the Bloomberg Helpdesk. I hope you can adapt this example to suit your needs - I'm not a Bloomberg expert so lots of testing on your side is required!

Hope that helps...

 

[waterboy202]

Hello,

When I try to run I try to run the macro on the bloomberg terminal it errors out on the below line of code. BLP_DATA_CTRLLib.BlpData I have the bloomberg excel software installed however it is still erroring out. I tried calling and messing the support desk however they were not much help. I think I might just need to install the BLP dictionary in excel however I can not find it. Any help?

Thank you.

 

[mccho]

Hi,

I just have a random question concerning actually coding in excel onto the Bloomberg terminal.

Are you writing code in excel on a bloomberg installed machine or are you coding directly into a bloomberg screen?

I am looking to setup an automatic e-mail for screen grabs from bloomberg to my workstation.

Thank you.
Edward

 

[Colin Legg]

Edward, this was VBA code written in Excel on a machine which had Bloomberg installed.

 

[Colin Legg]

Further OnTime discussion and suggestions on this thread:

http://www.mrexcel.com/forum/showthread.php?t=589938

 

[mccho]

Edward, this was VBA code written in Excel on a machine which had Bloomberg installed.

Hi Colin,

I'm trying to run certain commands on Bloomberg and copy and paste the data to excel.

Specifically, I want to get a set of index values and would like to know how I can code VBA to initiate these commands.

Thanks.

 

[sowosamma]

Hi everybody,

I know that this post is older, but I found it today when I was searching for a Bloomberg Problem.

On your Bloomberg terminal if you type in WAPI < GO > you will find listings of the Bloomberg API and downloadable examples.

Using the helpfile information in that area we can build a more robust solution to this using the Bloomberg Data Type Library. Go to Tools | References and add a reference to this library. This code can then be used to populate the cells:

Sub Test2()
    Dim vResults, vSecurities, vFields
    Dim objBloomberg As BLP_DATA_CTRLLib.BlpData
 
    'fill our arrays - must be 1 dimension so we transpose from the worksheet
    With Application.WorksheetFunction
        vSecurities = .Transpose(Sheet1.Range("B2:B4").Value)
        vFields = .Transpose(.Transpose(Range("C1:H1").Value))
    End With
 
    Set objBloomberg = New BLP_DATA_CTRLLib.BlpData
    objBloomberg.AutoRelease = False
 
    objBloomberg.Subscribe _
                Security:=vSecurities, _
                cookie:=1, _
                Fields:=vFields, _
                Results:=vResults
 
    Sheet1.Range("C2:H4").Value = vResults
End Sub

This works 100% time for me. I haven't seen any information on whether or not we need to explicitly unsubscribe/release the BlpData object, so I will leave this for you to research. If needs be you can always ask the Bloomberg Helpdesk. I hope you can adapt this example to suit your needs - I'm not a Bloomberg expert so lots of testing on your side is required!

Hope that helps...

This post is great! Thank you so much.

I have tried the formula Colin mentioned to load BDS-data but it doesn't work. In the API-Examples (c:\blp\api\apiv3\com data control\examples\vba) I have downloaded are only examples with extra classes, but None of them uses the BLP_DATA_CTRLLib. Does anyone know how to use BLP_DATA_CTRLLib with BDS to receive the result in a variable?

Best regards,
Andi