Bloomberg links and macros

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Bloomberg links and macros

  1. #1
    Board Regular
    Join Date
    Aug 2005
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Bloomberg links and macros

     
    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

  2. #2
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bloomberg links and macros

    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?

  3. #3
    Board Regular
    Join Date
    Aug 2005
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bloomberg links and macros

    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

  4. #4
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bloomberg links and macros

    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.

    Sheet1
    BCDEFGH
    1PX_LASTPX_BIDPX_ASKPX_LOWPX_HIGHPX_MID
    2AA UN Equity
    3AAPL UW Equity
    4ABT UN Equity
    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:
    Code:
    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:
    Code:
    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
    Sheet1
    BCDEFGH
    1PX_LASTPX_BIDPX_ASKPX_LOWPX_HIGHPX_MID
    2AA UN Equity12.612.612.6212.4212.6512.61
    3AAPL UW Equity172.93172.91172.93172173.13172.92
    4ABT UN Equity45.9145.8945.9145.2645.9145.9
    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...
    Last edited by Colin Legg; Sep 9th, 2009 at 09:23 AM.

  5. #5
    New Member
    Join Date
    Mar 2007
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bloomberg links and macros

    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.

  6. #6
    New Member
    Join Date
    Oct 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bloomberg links and macros

    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

  7. #7
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bloomberg links and macros

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

  8. #8
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bloomberg links and macros

    Further OnTime discussion and suggestions on this thread:

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

  9. #9
    New Member
    Join Date
    Oct 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bloomberg links and macros

    Quote Originally Posted by Colin Legg View Post
    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.

  10. #10
    New Member
    Join Date
    Nov 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bloomberg links and macros

      
    Hi everybody,

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

    Quote Originally Posted by Colin Legg View Post
    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:
    Code:
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

  

 

 

DMCA.com