?
Solved

Bloomberg Subscribe Function on Access Database

Posted on 2007-07-30
3
Medium Priority
?
7,196 Views
Last Modified: 2013-12-20
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?
 
0
Comment
Question by:greep
  • 2
3 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 19594402
At the very least you should be able to pass it the same way. Have you tried concatenating values together?

Leon
0
 
LVL 3

Expert Comment

by:ry_ashpool
ID: 19824177
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
 
LVL 3

Accepted Solution

by:
ry_ashpool earned 2000 total points
ID: 19824182
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question