WriteMatrixToRange result, Sheets( "Sheet2").Range( "_output")ĭebug.Print ((GetTickCount - startTime) / 1000) ' ' export variant array content into 2-dim array of strings ' and define Bloomberg field (only one!) to be retrieved Dim s() As String: s = matrixToSecurities(matrix)ĭim f() As String: ReDim f(0 To 0): f(0) = "PX_MID" ' ' create BCOM instance and retrieve market data Dim b As New BCOM_wrapper ' ' set range for input matrix in Excel and read values into variant array Set r_input = Sheets( "Sheet2").Range( "_input") ' Private Declare Function GetTickCount Lib "kernel32.dll" () As Long Private startTime As Long ' Private r_input As Range ' input range for Bloomberg tickers Private r_output As Range ' output range for Bloomberg data Private matrix As Variant ' input tickers matrix Private result As Variant ' bloomberg results ' Public Sub tester() For example, Cap volatility surface matrix for SEK currency is presented in the picture below. I have been using Bloomberg VCUB function to get volatility surface data from Bloomberg. Let us find a way to handle this input data (tickers) in a way, which enables us to create a single request for BCOM server. Now, back to our original problem (60 separate requests, intolerable processing time) which is not BCOM wrapper issue, but a consequence caused by the way we might handle the input data. BCOM wrapper was not originally meant to be used in repetitive data queries because of this reason. Why? Because for each query iteration, BCOM wrapper is opening connection and starting session with BCOM server. Alternatively, if we would like to process those tickers one by one (I assume the reader did this), the time elapsed for such query will be intolerable. This means, that for a single BCOM wrapper query, we have to read all those securities from some source into a one-dimensional array. However, for volatility surfaces, there is no such scheme available and all you have, is N amount of individual security tickers. With Bloomberg curves, such as USD swaps curve, you have Bloomberg ID for that curve and you can retrieve all members of that curve by using Bulk reference request and field name INDX_MEMBERS to retrieve all securities within that curve. In a nutshell, the user wants to retrieve Bloomberg volatility surface into Excel with BCOM API in a single request. Do you have any advice for a one request code, that could allow me to build one array with all my values?"
BLOOMBERG API VBA CODE
I tried the same with your code and all I managed to do is one request per maturity/strike, which means 60 separate requests. I usually use BDP functions with override fields. " Lets say I want to request a 6x10 FX volatility surface. One such an issue was also asked by one of my blog readers a couple of months ago: I have been planning to release a couple of new postings concerning Bloomberg market data API, based on some new things what I have learned, while working and doing things for other people. This has been clearly the most popular topic on this blog. I always feel a bit guilty about not responding to my readers, who might be having issues with Bloomberg API stuff.
0 kommentar(er)
