One such an issue was also asked by one of my blog readers a couple of months ago:
"Lets say I want to request a 6x10 FX volatility surface. I usually use BDP functions with override fields. I tried the same with your code and all I managed to do is one request per maturity/strike, which means 60 separate requests. It works but it's really time consuming. Do you have any advice for a one request code, that could allow me to build one array with all my values?"
In a nutshell, the user wants to retrieve Bloomberg volatility surface into Excel with BCOM API in a single request. 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. However, for volatility surfaces, there is no such scheme available and all you have, is N amount of individual security tickers.
This means, that for a single BCOM wrapper query, we have to read all those securities from some source into a one-dimensional array. 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. Why? Because for each query iteration, BCOM wrapper is opening connection and starting session with BCOM server. BCOM wrapper was not originally meant to be used in repetitive data queries because of this reason.
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. 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.
STEP ONE: Bloomberg surface data in VCUBI have been using Bloomberg VCUB function to get volatility surface data from Bloomberg. For example, Cap volatility surface matrix for SEK currency is presented in the picture below.
STEP TWO: Excel data range configurationsNow, let us retrieve tickers first. In this screen, go to Actions - Export to Excel - Tickers. CopyPaste
Cap market tickers into a new Excel workbook (blue area in the picture below).
Blue area has all security tickers what we just downloaded from VCUB. Pink area will be filled with values retrieved from Bloomberg with BCOM wrapper for each security ticker. Define Excel range name for the blue area as "_input" and Excel range name for the pink area as "_output".
STEP THREE: VBA programNext, add new standard VBA module into your VB editor and copyPaste the following program.
STEP FOUR: test runRemember also to include BCOM wrapper into your program. Follow all instructions given in here. When we run this example program, the result should be the following.
If we cross check the corresponding values for SEK Cap volatilities (PX_MID) with Bloomberg BDP Excel worksheet functions, we should get pretty much the same values what we will get from BCOM server with wrapper. Now, if you would like to change your volatility feed (different currency or instrument type), just import new tickers from Bloomberg VCUB into your Excel, define new input range ("_input") and new output range ("_output") and run the program. In VBA program, define correct Bloomberg field name. At the moment, this has been hard-coded to be PX_MID. That's basically all you have to do.
I am not saying that this is absolutely the best way for handling this data, but it is working well and it is efficient enough, even there are some additional twists needed with data input and output. I ran 10 separate data retrieving processes with BCOM wrapper and calculated average of those processing times. For those 70 SEK tickers, processing time (from reading data to writing data) was 1.5 seconds and for example, for USD Cap volatility data (255 tickers), average processing time was 2.2 seconds.
Thanks for reading.