Bloomberg Subscribe Function on Access Database

Hi,
I have inherited some Excel Sheets and Access databases which use the BLP Add-ins to retrive data from Bloomberg.
The Access database is using the Subscribe method - example code below.
      '---------------------------------------------------------------------------------------------------
                     Public WithEvents objBloomberg As BLP_DATA_CTRLLib.BlpData
      Public Sub MakeDataRequest(Security() As String, Fields() As String, Optional OverFields As Variant, Optional OverValues As Variant)
      '  Where Security is an array of Security Numbers,
                     'Fields is an array of Bloomberg field mnemonics,
                     'OverFields and OverValues are not used -no idea what they are supposed to do.
      Dim nCount As Long
      Dim nMaxCount As Long
      nMaxCount = UBound(Security, 1)
      objBloomberg.AutoRelease = False
      For nCount = 0 To nMaxCount
                       objBloomberg.Subscribe Security(nCount), nCount, Fields, OverFields, OverValues, Monitor:=False
               nSendCount = nSendCount + 1
      Next nCount
      objBloomberg.Flush
      End Sub
      'data is then received by a sub called DataCallback, and this works ok.
      Public Sub DataCallback(nCookie As Long, vtData As Variant)
      '---------------------------------------------------------------------------------------------------
This all works fine for retreiving current data.
 My problem is that I need to be able to retreive data for a different Settlement Date.  

In Excel with the BLP function, this is easy as the required date can be passed as part of the field array e.g.
BLP(123456 CORP,[SETTLE_DT=20070720,GOVT_IDX_RATIO])

Is there a way to do something similar with the Access Subscribe function?
 
greepAsked:
Who is Participating?
 
ry_ashpoolConnect With a Mentor Commented:
Here is the code for the whole class module I use:  I then call these routines from inside my main code:

'Option Explicit
'Object variable to hold a reference to the Bloomberg data control
Private WithEvents oBBG As BLP_DATA_CTRLLib.BlpData
'member variable to hold sheet reference (to which data will be written back)
Private m_sheetref As Worksheet
Dim CRNCY As Boolean
Dim fxrates As Boolean
Dim nStartRow As Integer
Dim nStartColumn As Integer
Property Let Datasheet(nsheet As Worksheet)
    'set's the sheet to which data is written back
    Set m_sheetref = nsheet
End Property
Private Sub Class_Initialize()
'when class initializes, instantiate oBBG
    Set oBBG = New BLP_DATA_CTRLLib.BlpData
End Sub
Private Sub Class_Terminate()
'when class terminates, destroy oBBG
    Set oBBG = Nothing
End Sub
Public Sub AsyncHistoryReq(SecurityList As Variant, FieldList As Variant, _
    dtStart As Date, dtCur As Variant, dtEnd As Date)
   CRNCY = False
   
   Dim secLoop
   'Set the properties related to historical data
   With oBBG
        .DisplayNonTradingDays = AllCalendar
        .Periodicity = bbDaily
        If UCase(Range("b6").Value) = "EQUITY" Then .ShowYields = True
       
       
        .NonTradingDayValue = PreviousDays
           .SubscriptionMode = BySecurity
           'Hold the request
           .AutoRelease = False
           For secLoop = LBound(SecurityList, 1) To UBound(SecurityList, 1)
               .GetHistoricalData2 SecurityList(secLoop), secLoop, FieldList, _
                     dtStart, dtCur(secLoop), dtEnd
           Next
           'Flush out the request
           .Flush
       
   End With

End Sub
Public Sub AsyncCurrReq(SecurityList As Variant, FieldList As Variant)
Dim secLoop
CRNCY = True
Dim oField As Variant
Dim oValue As Variant
   'Set the properties related to historical data
   With oBBG
        .DisplayNonTradingDays = AllCalendar
        .Periodicity = bbDaily
       
        .NonTradingDayValue = PreviousDays
           .SubscriptionMode = BySecurity
           'Hold the request
           .AutoRelease = False
           Set oField = Nothing
           Set oValue = Nothing
           If Range("b6").Value = True Then oField = "Eqy_fund_crncy"
           If Range("b6").Value = True Then oValue = "USD"
           'First Cell Never Works for Subscribe... Never gets to output.
           'These next two lines force this one cell to immediately process.
           .Subscribe SecurityList(2), 1, FieldList, oField, oValue, dtEnd
           m_sheetref.Range("B9") = dtEnd(0, 0)
           
           
           For secLoop = LBound(SecurityList, 1) To UBound(SecurityList, 1)
               .Subscribe SecurityList(secLoop), secLoop, FieldList, oField, oValue, dtEnd
           
           Next
           'Flush out the request
           .Flush
       
   End With






End Sub
Private Sub oBBG_Data(Security As Variant, cookie As Long, Fields As Variant, data As Variant, Status As Long)
       
       
       If CRNCY = False Then m_sheetref.Range(Cells(cookie + 8, 3), Cells(cookie + 8, 3)) = data(0, 1)
       If CRNCY = True Then m_sheetref.Range(Cells(cookie + 8, 2), Cells(cookie + 8, 2)) = data
       
End Sub
0
 
leonstrykerCommented:
At the very least you should be able to pass it the same way. Have you tried concatenating values together?

Leon
0
 
ry_ashpoolCommented:
Yes you can do it easy:

Instead of .subscribe you can use .gethistoricaldata or .gethistoricaldata2

For example
objBloomberg.gethistoricaldata2 Security(nCount), nCount, Fields, dateStart, Currency, dateEnd

so you could return microsoft closing prices like this
objBloomberg.gethistoricaldata2 "MSFT US",1, "px_last", "01/08/2008","USD", "02/08/2008"

You can return a single date if you make dateStart and dateEnd equal:  Also if you just want to use base currency then you can use .gethistoricaldata instead of .gethistoricaldata2


Here is an example of me using this in excel.  You should be able to adapt it for access pretty easy.

Public Sub AsyncHistoryReq(SecurityList As Variant, FieldList As Variant, _
    dtStart As Date, dtCur As Variant, dtEnd As Date)
   CRNCY = False
   
   Dim secLoop
   'Set the properties related to historical data
   With oBBG
        .DisplayNonTradingDays = AllCalendar
        .Periodicity = bbDaily
        If UCase(Range("b6").Value) = "EQUITY" Then .ShowYields = True
       
       
        .NonTradingDayValue = PreviousDays
           .SubscriptionMode = BySecurity
           'Hold the request
           .AutoRelease = False
           For secLoop = LBound(SecurityList, 1) To UBound(SecurityList, 1)
               .GetHistoricalData2 SecurityList(secLoop), secLoop, FieldList, _
                     dtStart, dtCur(secLoop), dtEnd
           Next
           'Flush out the request
           .Flush
       
   End With

End Sub
Public Sub AsyncCurrReq(SecurityList As Variant, FieldList As Variant)
Dim secLoop
CRNCY = True
Dim oField As Variant
Dim oValue As Variant
   'Set the properties related to historical data
   With oBBG
        .DisplayNonTradingDays = AllCalendar
        .Periodicity = bbDaily
       
        .NonTradingDayValue = PreviousDays
           .SubscriptionMode = BySecurity
           'Hold the request
           .AutoRelease = False
           Set oField = Nothing
           Set oValue = Nothing
           If Range("b6").Value = True Then oField = "Eqy_fund_crncy"
           If Range("b6").Value = True Then oValue = "USD"
           'First Cell Never Works for Subscribe... Never gets to output.
           'These next two lines force this one cell to immediately process.
           .Subscribe SecurityList(2), 1, FieldList, oField, oValue, dtEnd
           m_sheetref.Range("B9") = dtEnd(0, 0)
           
           
           For secLoop = LBound(SecurityList, 1) To UBound(SecurityList, 1)
               .Subscribe SecurityList(secLoop), secLoop, FieldList, oField, oValue, dtEnd
           
           Next
           'Flush out the request
           .Flush
       
   End With






End Sub
Private Sub oBBG_Data(Security As Variant, cookie As Long, Fields As Variant, data As Variant, Status As Long)
       
       
       If CRNCY = False Then m_sheetref.Range(Cells(cookie + 8, 3), Cells(cookie + 8, 3)) = data(0, 1)
       If CRNCY = True Then m_sheetref.Range(Cells(cookie + 8, 2), Cells(cookie + 8, 2)) = data
       
End Sub



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.