C++11 : Wrapper for Bloomberg API Reference Data

Bloomberg is offering an access to its market data API with several different programming languages. Previously, I have presented my wrappers for VBA and C#. This time, I wanted to open up an implementation for C++ wrapper, which covers different types of reference data requests (snapshot, historical, intraday bar, intraday tick). For reference data, API functionality is basically the same for all types of data request types : create inputs (security, field), start session, open service, create request, send request to server, poll for results, unzip messages from server and finally, stop session.

Notes

There were some tough decisions to make. In my C# wrapper, I was sub-classing algorithm for handling messages from server (unzipping message content into data structure). Within this implementation, I made a decision to prefer straightforward simplicity, even it would mean a bit of data duplication. Personally, when using this kind of functionality, I would prefer to have just one single class (one header, one implementation), instead of having several classes.

Another big issue was concerning data output coming from Bloomberg server back to a client : how to pack all requested data into data structure to be easily used by a client ? In C# implementation we had dynamic data type, which enables us to create arrays of dynamic data types. However, in C++ there are no heterogeneous data structures available, besides std::tuple. Initially, I was considering to use tuple as result data structure, but I decided to reject the idea due to material increase in general complexity. I decided to return all possible data types from Bloomberg server as std::string within result data structure. It would be then up to a client to convert these values "back to their origins", by using std::stod or some other conversion method.

For the sake of clarity, I have created type definitions for several nested data structures used in the program : Vector [std::vector<std::string>], Matrix for all types of reference data [std::vector<Vector>] and Cube for historical time-series data [std::vector<Matrix>].

Finally, in order to "make it happen", a set of header files and libraries has to be configured into Visual Studio project. Extremely well-written and complete step-by-step tutorial is available in here.

Header file

#pragma once
//
#include <memory>
#include <string>
#include <vector>
#include <algorithm>
#include <blpapi_session.h>
//
namespace MikeJuniperhillBloombergCPPDesktopAPI
{
 using namespace BloombergLP::blpapi;
 using Vector = std::vector<std::string>; // string vector
 using Matrix = std::vector<Vector>; // string matrix
 using Cube = std::vector<Matrix>; // string cube
 enum ENUM_PRICING_OPTION { PRICING_OPTION_PRICE, PRICING_OPTION_YIELD };
 enum ENUM_PERIODICITY_ADJUSTMENT { ACTUAL, CALENDAR, FISCAL };
 enum ENUM_PERIODICITY_SELECTION { DAILY, WEEKLY, MONTHLY, QUARTERLY, SEMI_ANNUALLY, YEARLY };
 enum ENUM_NON_TRADING_DAY_FILL_OPTION { NON_TRADING_WEEKDAYS, ALL_CALENDAR_DAYS, ACTIVE_DAYS_ONLY };
 enum ENUM_NON_TRADING_DAY_FILL_METHOD { PREVIOUS_VALUE, NIL_VALUE };
 //
 class BBCOMMDataRequest
 {
 public:
  BBCOMMDataRequest(std::string serverHost = "localhost", unsigned short serverPort = 8194);
  void Start();
  void GetReferenceData(Vector& securities, Vector& fields, Matrix& result,
   Vector& overrideFields = Vector(), Vector& overrideValues = Vector());
  void GetHistoricalData(Vector& securities, Vector& fields, Cube& result,
   std::string startDate, std::string endDate,
   ENUM_PRICING_OPTION pricingOption = PRICING_OPTION_PRICE,
   ENUM_PERIODICITY_SELECTION periodicitySelection = DAILY,
   ENUM_PERIODICITY_ADJUSTMENT periodicityAdjustment = ACTUAL,
   ENUM_NON_TRADING_DAY_FILL_OPTION nonTradingDayFillOption = ALL_CALENDAR_DAYS,
   ENUM_NON_TRADING_DAY_FILL_METHOD nonTradingDayFillMethod = PREVIOUS_VALUE,
   std::string overrideCurrency = std::string(),
   Vector& overrideFields = Vector(),
   Vector& overrideValues = Vector());
  void GetIntradayBarData(std::string security, Vector& fields, Matrix& result, 
   std::string eventType, int intervalInMinutes, Datetime start, Datetime end);
  void GetIntradayTickData(std::string security, Vector& fields, Matrix& result, Vector& eventTypes,
   Datetime start, Datetime end, bool includeConditionCodes = false, bool includeExchangeCodes = false);
  void Stop();
 private:
  std::unique_ptr<SessionOptions> sessionOptions;
  std::unique_ptr<Session> session;
  std::unique_ptr<Service> service;
  std::unique_ptr<Request> request;
  std::string serverHost;
  unsigned short serverPort;
 };
}
//

Implementation file

#pragma once
#include "BBCOMMDataRequest.h"
using namespace MikeJuniperhillBloombergCPPDesktopAPI;
//
BBCOMMDataRequest::BBCOMMDataRequest(std::string serverHost, unsigned short serverPort) 
 : serverHost(serverHost), serverPort(serverPort)
{
 // ctor with default parameters for server hosting and port
}
void BBCOMMDataRequest::Start()
{
 // create objects : session options and session
 sessionOptions = std::unique_ptr<SessionOptions>(new SessionOptions);
 sessionOptions->setServerHost(serverHost.c_str());
 sessionOptions->setServerPort(serverPort);
 session = std::unique_ptr<Session>(new Session(*sessionOptions));
 //
 // start session and open service, throw if not succeeded
 if (!session->start()) throw std::exception("session not started");
 if (!session->openService("//blp/refdata")) throw std::exception("service not opened");
}
void BBCOMMDataRequest::GetReferenceData(Vector& securities, Vector& fields, Matrix& result,
 Vector& overrideFields, Vector& overrideValues)
{
 // initialize result data structure with default '#N/A' for all items
 for (unsigned int i = 0; i < securities.size(); ++i)
 {
  Vector innerVector(fields.size(), "#N/A");
  result.push_back(innerVector);
 }
 // create objects : service and request
 service = std::unique_ptr<Service>(new Service(session->getService("//blp/refdata")));
 request = std::unique_ptr<Request>(new Request(service->createRequest("ReferenceDataRequest")));
 //
 // append securities and field requests into request object
 std::for_each(securities.begin(), securities.end(), [this](std::string s) { request->append("securities", s.c_str()); });
 std::for_each(fields.begin(), fields.end(), [this](std::string f) { request->append("fields", f.c_str()); });
 //
 // conditionally, append overrides into request object
 if (overrideFields.size() > 0)
 {
  Element requestOverrides = request->getElement("overrides");
  for (unsigned int i = 0; i < overrideFields.size(); ++i)
  {
   Element requestOverride = requestOverrides.appendElement();
   requestOverride.setElement("fieldId", overrideFields[i].c_str());
   requestOverride.setElement("value", overrideValues[i].c_str());
  }
 }
 // finally send data request to server
 session->sendRequest(*request);
 //
 // start polling server response for data request
 bool isProcessing = true;
 while (isProcessing)
 {
  // catch all 'response-typed' events from server
  Event bEvent = session->nextEvent();
  if ((bEvent.eventType() == Event::PARTIAL_RESPONSE) || (bEvent.eventType() == Event::RESPONSE))
  {
   // create iterator for accessing server message
   MessageIterator bMessageIterator(bEvent);
   while (bMessageIterator.next())
   {
    // get access to message, extract all included securities
    Message bMessage = bMessageIterator.message();
    Element bSecurities = bMessage.getElement("securityData");
    int nSecurities = bSecurities.numValues();
    //
    // loop through all securities in current server response batch
    for (int i = 0; i < nSecurities; ++i)
    {
     // extract one security and all available fields for this security
     Element bSecurity = bSecurities.getValueAsElement(i);
     Element bFields = bSecurity.getElement("fieldData");
     int sequenceNumber = bSecurity.getElementAsInt32("sequenceNumber");
     int nFieldNames = fields.size();
     //
     // loop through all requested fields
     for (int j = 0; j < nFieldNames; ++j)
     {
      // assign string value only if the field is included in delivery
      if (bFields.hasElement(fields[j].c_str()))
      {
       Element bField = bFields.getElement(fields[j].c_str());
       result[sequenceNumber][j] = bField.getValueAsString();
      }
     }
    }
   }
   // when event type is response, there will be no more messages from server
   if (bEvent.eventType() == Event::RESPONSE) isProcessing = false;
  }
 }
}
void BBCOMMDataRequest::GetHistoricalData(Vector& securities, Vector& fields, Cube& result,
 std::string startDate, std::string endDate,
 ENUM_PRICING_OPTION pricingOption,
 ENUM_PERIODICITY_SELECTION periodicitySelection,
 ENUM_PERIODICITY_ADJUSTMENT periodicityAdjustment,
 ENUM_NON_TRADING_DAY_FILL_OPTION nonTradingDayFillOption,
 ENUM_NON_TRADING_DAY_FILL_METHOD nonTradingDayFillMethod,
 std::string overrideCurrency,
 Vector& overrideFields,
 Vector& overrideValues)
{
 bool resultHasDimension = false;
 // create objects : service and request
 service = std::unique_ptr<Service>(new Service(session->getService("//blp/refdata")));
 request = std::unique_ptr<Request>(new Request(service->createRequest("HistoricalDataRequest")));
 //
 // append securities and field requests into request object
 std::for_each(securities.begin(), securities.end(), [this](std::string s) { request->append("securities", s.c_str()); });
 std::for_each(fields.begin(), fields.end(), [this](std::string f) { request->append("fields", f.c_str()); });
 //
 // conditionally, append overrides into request object
 if (overrideFields.size() > 0)
 {
  Element requestOverrides = request->getElement("overrides");
  for (unsigned int i = 0; i < overrideFields.size(); ++i)
  {
   Element requestOverride = requestOverrides.appendElement();
   requestOverride.setElement("fieldId", overrideFields[i].c_str());
   requestOverride.setElement("value", overrideValues[i].c_str());
  }
 }
 //
 // set optional parameters for historical data request
 request->set("startDate", startDate.c_str());
 request->set("endDate", endDate.c_str());
 request->set("pricingOption", pricingOption);
 request->set("periodicityAdjustment", periodicityAdjustment);
 request->set("periodicitySelection", periodicitySelection);
 request->set("nonTradingDayFillOption", nonTradingDayFillOption);
 request->set("nonTradingDayFillMethod", nonTradingDayFillMethod);
 if (!overrideCurrency.empty()) request->set("currency", overrideCurrency.c_str());
 //
 // finally send data request to server
 session->sendRequest(*request);
 //
 // start polling server response for data request
 bool isProcessing = true;
 while (isProcessing)
 {
  // catch all 'response-typed' events from server
  Event bEvent = session->nextEvent();
  //
  if ((bEvent.eventType() == Event::PARTIAL_RESPONSE) || (bEvent.eventType() == Event::RESPONSE))
  {
   // create iterator for accessing server message
   MessageIterator bMessageIterator(bEvent);
   // unzip and pack messages received from BBCOMM server
   while (bMessageIterator.next())
   {
    // receive one security per message and multiple messages per event
    Message bMessage = bMessageIterator.message();
    Element bSecurity = bMessage.getElement("securityData");
    Element bFields = bSecurity.getElement("fieldData");
    int sequenceNumber = bSecurity.getElementAsInt32("sequenceNumber");
    int nFieldNames = fields.size();
    unsigned int nObservationDates = bFields.numValues();
    //
    // the exact dimension for result data structure will be known 
    // only when the response has been received from BBCOMM server
    if (!resultHasDimension)
    {
     // initialize result data structure with default '#N/A' for all items
     for (unsigned int i = 0; i < securities.size(); ++i)
     {
      Matrix outerVector;
      for (unsigned int j = 0; j < nObservationDates; j++)
      {
       Vector innerVector(fields.size() + 1, "#N/A");
       outerVector.push_back(innerVector);
      }
      result.push_back(outerVector);
     }
     resultHasDimension = true;
    }
    // loop through all observation dates
    for (unsigned int i = 0; i < nObservationDates; ++i)
    {
     Element observationDateFields = bFields.getValueAsElement(i);
     result[sequenceNumber][i][0] = observationDateFields.getElementAsString("date");
     // loop through all requested fields for a given observation date
     // and pack results data into data structure
     for (int j = 0; j < nFieldNames; j++)
     {
      // pack field value into data structure only if such value is available
      if (observationDateFields.hasElement(fields[j].c_str()))
      {
       result[sequenceNumber][i][j + 1] = observationDateFields.getElement(fields[j].c_str()).getValueAsString();
      }
     }
    }
   }
   // when event type is response, there will be no more messages coming from server
   if (bEvent.eventType() == Event::RESPONSE) isProcessing = false;
  }
 }
}
void BBCOMMDataRequest::GetIntradayBarData(std::string security, Vector& fields, Matrix& result, std::string eventType, int intervalInMinutes,
 Datetime start, Datetime end)
{
 // create objects : service and request
 service = std::unique_ptr<Service>(new Service(session->getService("//blp/refdata")));
 request = std::unique_ptr<Request>(new Request(service->createRequest("IntradayBarRequest")));
 request->set("security", security.c_str());
 request->set("eventType", eventType.c_str());
 request->set("interval", intervalInMinutes);
 request->set("startDateTime", start);
 request->set("endDateTime", end);
 // finally send data request to server
 session->sendRequest(*request);
 //
 // start polling server response for data request
 bool isProcessing = true;
 while (isProcessing)
 {
  // catch all 'response-typed' events from server
  Event bEvent = session->nextEvent();
  //
  if ((bEvent.eventType() == Event::PARTIAL_RESPONSE) || (bEvent.eventType() == Event::RESPONSE))
  {
   // create iterator for accessing server message
   MessageIterator bMessageIterator(bEvent);
   // unzip and pack messages received from BBCOMM server
   while (bMessageIterator.next())
   {
    // receive one security per message and multiple messages per event
    Message bMessage = bMessageIterator.message();
    Element bData = bMessage.getElement("barData").getElement("barTickData");
    int nBars = bData.numValues();
    //
    for (int i = 0; i < nBars; ++i)
    {
     Element bBar = bData.getValueAsElement(i);
     if (!bBar.isNull())
     {
      Vector innerVector;
      for (unsigned int j = 0; j < fields.size(); ++j)
      {
       if (bBar.hasElement(fields[j].c_str()))
        innerVector.push_back(bBar.getElementAsString(fields[j].c_str()));
      }
      result.push_back(innerVector);
     }
    }
   }
   // when event type is response, there will be no more messages coming from server
   if (bEvent.eventType() == Event::RESPONSE) isProcessing = false;
  }
 }
}
void BBCOMMDataRequest::GetIntradayTickData(std::string security, Vector& fields, Matrix& result, Vector& eventTypes,
 Datetime start, Datetime end, bool includeConditionCodes, bool includeExchangeCodes)
{
 // create objects : service and request
 service = std::unique_ptr<Service>(new Service(session->getService("//blp/refdata")));
 request = std::unique_ptr<Request>(new Request(service->createRequest("IntradayTickRequest")));
 request->set("security", security.c_str());
 request->set("startDateTime", start);
 request->set("endDateTime", end);
 std::for_each(eventTypes.begin(), eventTypes.end(), [this](std::string e) { request->append("eventTypes", e.c_str()); });
 if (includeConditionCodes) request->set("includeConditionCodes", true);
 if (includeExchangeCodes) request->set("includeExchangeCodes", true);
 // finally send data request to server
 session->sendRequest(*request);
 //
 // start polling server response for data request
 bool isProcessing = true;
 while (isProcessing)
 {
  // catch all 'response-typed' events from server
  Event bEvent = session->nextEvent();
  //
  if ((bEvent.eventType() == Event::PARTIAL_RESPONSE) || (bEvent.eventType() == Event::RESPONSE))
  {
   // create iterator for accessing server message
   MessageIterator bMessageIterator(bEvent);
   // unzip and pack messages received from BBCOMM server
   while (bMessageIterator.next())
   {
    // receive one security per message and multiple messages per event
    Message bMessage = bMessageIterator.message();
    Element bData = bMessage.getElement("tickData").getElement("tickData");
    int nTicks = bData.numValues();
    //
    for (int i = 0; i < nTicks; ++i)
    {
     Element bItem = bData.getValueAsElement(i);
     if (!bItem.isNull())
     {
      Vector innerVector;
      for (unsigned int j = 0; j < fields.size(); ++j)
      {
       if (bItem.hasElement(fields[j].c_str()))
        innerVector.push_back(bItem.getElementAsString(fields[j].c_str()));
      }
      // include conditionally requested condition and exchange codes
      if (bItem.hasElement("conditionCodes") && (includeConditionCodes))
       innerVector.push_back(bItem.getElementAsString("conditionCodes"));
      if (bItem.hasElement("exchangeCodes") && (includeExchangeCodes))
       innerVector.push_back(bItem.getElementAsString("exchangeCodes"));
      result.push_back(innerVector);
     }
    }
   }
   // when event type is response, there will be no more messages coming from server
   if (bEvent.eventType() == Event::RESPONSE) isProcessing = false;
  }
 }
}
void BBCOMMDataRequest::Stop()
{
 session->stop();
}

Tester file

#include <iostream>
#include "BBCOMMDataRequest.h"
//
namespace MJ = MikeJuniperhillBloombergCPPDesktopAPI;
void PrintMatrix(MJ::Matrix& result);
void PrintCube(MJ::Cube& result);
void CreateEquitySecurityList(std::vector<std::string>& securities);
void CreateRateSecurityList(std::vector<std::string>& securities);
//
int main()
{
 try
 {
  // start bloomberg session
  MJ::BBCOMMDataRequest bloomberg;
  bloomberg.Start();
  //
  std::cout << "CASE 1 : create reference data request without overrides >" << std::endl;
  // create list of rate securities and fields, request data and print result
  MJ::Vector securities;
  CreateRateSecurityList(securities);
  MJ::Vector fields{ "PARSEKYABLE_DES", "PX_LAST" };
  MJ::Matrix matrixResult;
  bloomberg.GetReferenceData(securities, fields, matrixResult);
  PrintMatrix(matrixResult);
  //
  std::cout << "CASE 2 : create reference data request with overrides >" << std::endl;
  // create list of equity securities and fields, request data and print result
  CreateEquitySecurityList(securities);
  fields.push_back("BEST_PE_RATIO");
  MJ::Vector overrideFields{ "BEST_FPERIOD_OVERRIDE" };
  MJ::Vector overrideValues{ "3FY" };
  matrixResult.clear();
  bloomberg.GetReferenceData(securities, fields, matrixResult, overrideFields, overrideValues);
  PrintMatrix(matrixResult);
  //
  std::cout << "CASE 3 : create historical data request for previous securities >" << std::endl;
  // use previous securities, re-create fields, request data and print result
  fields.clear();
  fields.push_back("PX_LAST");
  MJ::Cube cubeResult;
  // bloomberg API date format : 'YYYYMMDD'
  std::string startDate = "20170301";
  std::string endDate = "20170322";
  // request actual daily frequency, but only for weekdays and prices converted to JPY
  bloomberg.GetHistoricalData(securities, fields, cubeResult, startDate, endDate, 
   MJ::PRICING_OPTION_PRICE, MJ::DAILY, MJ::ACTUAL, MJ::NON_TRADING_WEEKDAYS, MJ::PREVIOUS_VALUE, "JPY");
  PrintCube(cubeResult);
  //
  std::cout << "CASE 4 : create historical data request for list of securities >" << std::endl;
  // create list of rate securities, use previous field, request data and print result
  CreateRateSecurityList(securities);
  cubeResult.clear();
  bloomberg.GetHistoricalData(securities, fields, cubeResult, startDate, endDate);
  PrintCube(cubeResult);
  //
  std::cout << "CASE 5 : create intraday bar data request for single security >" << std::endl;
  // create one security, request data and print result
  std::string security = "GOOGL UW Equity";
  MJ::Vector barFields{ "time", "open" , "high", "low", "close" };
  std::string eventType = "TRADE";
  int intervalInMinutes = 1;
  MJ::Datetime start;
  start.setDate(2017, 3, 22);
  start.setTime(15, 0, 0);
  MJ::Datetime end;
  end.setDate(2017, 3, 22);
  end.setTime(15, 10, 0);
  matrixResult.clear();
  bloomberg.GetIntradayBarData(security, barFields, matrixResult, eventType, intervalInMinutes, start, end);
  PrintMatrix(matrixResult);
  //
  std::cout << "CASE 6 : create intraday tick data request for single security >" << std::endl;
  // use previous security, request data and print result
  MJ::Vector tickFields{ "time", "value", "size", "type" };
  MJ::Vector eventTypes{ "TRADE" };
  matrixResult.clear();
  bloomberg.GetIntradayTickData(security, tickFields, matrixResult, eventTypes, start, end, true, true);
  PrintMatrix(matrixResult);
  //
  // stop bloomberg session
  bloomberg.Stop();
 }
 catch (std::exception& e)
 {
  std::cout << e.what() << std::endl;
 }
 return 0;
}
//
void PrintMatrix(MJ::Matrix& result)
{
 for (unsigned int i = 0; i < result.size(); ++i)
 {
  for (unsigned int j = 0; j < result[i].size(); ++j)
  {
   std::cout << result[i][j] << std::endl;
  }
 }
 std::cout << std::endl;
}
//
void PrintCube(MJ::Cube& result)
{
 for (unsigned int i = 0; i < result.size(); ++i)
 {
  for (unsigned int j = 0; j < result[i].size(); ++j)
  {
   for (unsigned int k = 0; k < result[i][j].size(); k++)
   {
    std::cout << result[i][j][k] << std::endl;
   }
  }
 }
 std::cout << std::endl;
}
//
void CreateRateSecurityList(std::vector<std::string>& securities)
{
 // EUR Euribor vs. 6M swap curve
 securities.clear();
 securities.push_back("EONIA Index"); securities.push_back("EUR001W Index");
 securities.push_back("EUR001M Index"); securities.push_back("EUR002M Index");
 securities.push_back("EUR003M Index"); securities.push_back("EUR006M Index");
 securities.push_back("EUFR0F1 Curncy"); securities.push_back("EUFR0G1A Curncy");
 securities.push_back("EUFR0H1B Curncy"); securities.push_back("EUFR0I1C Curncy");
 securities.push_back("EUFR0J1D Curncy"); securities.push_back("EUFR0K1E Curncy");
 securities.push_back("EUFR011F Curncy"); securities.push_back("EUSA2 Curncy");
 securities.push_back("EUSA3 Curncy"); securities.push_back("EUSA4 Curncy");
 securities.push_back("EUSA5 Curncy"); securities.push_back("EUSA6 Curncy");
 securities.push_back("EUSA7 Curncy"); securities.push_back("EUSA8 Curncy");
 securities.push_back("EUSA9 Curncy"); securities.push_back("EUSA10 Curncy");
 securities.push_back("EUSA11 Curncy"); securities.push_back("EUSA12 Curncy");
 securities.push_back("EUSA15 Curncy"); securities.push_back("EUSA20 Curncy");
 securities.push_back("EUSA25 Curncy"); securities.push_back("EUSA30 Curncy");
 securities.push_back("EUSA35 Curncy"); securities.push_back("EUSA40 Curncy");
 securities.push_back("EUSA45 Curncy"); securities.push_back("EUSA50 Curncy");
}
//
void CreateEquitySecurityList(std::vector<std::string>& securities)
{
 // some equities
 securities.clear();
 securities.push_back("GOOGL UW Equity");
 securities.push_back("YHOO UW Equity");
 securities.push_back("FB UW Equity");
 securities.push_back("EBAY UW Equity");
}
//

Finally, thanks a lot for reading my blog.
-Mike

VBA wrapper for ADODB objects

Currently, for all Excel/VBA database-related working, I have been using Microsoft ADO (ActiveX Data Object library). It's already a bit old technology already and its enhanced brother ADO.NET is very unfortunately available only for .NET. If ADO is completely new topic for you, there is a lot of information available in Google. You should get yourself familiar with the following ADO objects and their corresponding important methods and properties to get started:

Connection (open, close, state)
Recordset (open, close, state, fields)

With these methods, properties and some good working example, you should have enough information to understand and learn to work with ADO connection and recordset.

In this post, I will open up my VBA wrapper for Microsoft ADO. There is a couple of things you should be aware. ADO is technically speaking dll library, what should be referenced before you can use it. Now, you have two options:

Early binding

You can reference it in a "standard way": (VB editor -- Tools -- Option -- Microsoft ActiveX Data Objects 6.X Library). This is called early binding. Now, with this approach, you have to do this referencing every time you import your custom VBA wrapper class into any new VBA project. The upside is, that all ADO object methods and properties are available in your intellisense. When you use early binding, you create your connection object like this:

Dim cn As New ADODB.Connection

Late binding

In this case, you do not need to do any dll referencing in your VB editor. You are doing it implicitly in your program. The only (AFAIK) notably downside is, that all different object methods and properties are not available in your intellisense anymore. When you use late binding, you create your connection object like this:

Dim cn As Object: Set cn = CreateObject(“ADODB.Connection”)

VBA ADO Wrapper interface

Example wrapper has only one public interface function "cGetRecordset", which returns an object for its caller (ADODB recordset) and takes in four arguments.

cUserID - your personal database username (can be empty string)
cPassword - your personal database password  (can be empty string)
cSqlQueryString - working SQL string
cDSN - connection string

I think that all the other arguments, except connection string should be self-explainable. In a connection string, we define the protocol, what we are using for getting an access to a database. Different database types are requiring different protocols for accessing them. Moreover, in a connection string we define data source from where the data is going to be retrieved. For working examples, the following website is an excellent reference for different connection strings available for numerous different databases: http://connectionstrings.com/

Below is my example tester program and wrapper class for retrieving data from MS Access database. Note, that I am using late-binding, in order to avoid any dll referencing when I want to use the wrapper in my new VBA projects. Note also, that the wrapper returns an object, having a type of ADODB recordset. You have to know how to "disassemble" data from ADO recordset back to program-level. Example is given in a tester program (NxM matrix).

Now, that's all I wanted to share about Microsoft ADO this time. Personally, I find it to be flexible tool for all database-related work in Excel/VBA. Have a nice day!
-Mike

' TESTER PROGRAM (STANDARD VBA MODULE)
'
Option Explicit
'
Sub tester()
    '
    On Error GoTo errorHandler
    '
    Dim data As New ADO_wrapper
    Dim sqlQueryString As String: sqlQueryString = "SELECT * FROM bonds"
    Dim connectionString As String: connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mka\temp\bondPosition.accdb;"
    '
    Dim result As Object
    Set result = data.cGetRecordset(connectionString, sqlQueryString)
    '
    ' Do your things with data here
    Dim i As Long, j As Long
    For i = 1 To result.RecordCount
        For j = 1 To result.Fields.Count
            Debug.Print result.Fields(j - 1)
        Next j
        result.movenext
    Next i
    '
    Set result = Nothing
    Set data = Nothing
    Exit Sub
errorHandler:
    Debug.Print Err.Source & ", " & Err.Description
End Sub
'

' ADO WRAPPER (VBA CLASS MODULE)
'
Option Explicit
'
Private Const CONST_AD_USE_CLIENT = 3
Private Const CONST_AD_OPEN_STATIC = 3
Private Const CONST_AD_LOCK_READONLY = 1
'
Private cUserID As String
Private cPassword As String
Private cDSN As String
Private cSqlQueryString As String
'
Private cConnection As Object 'ADODB.Connection
Private cRecordset As Object 'ADODB.Recordset
'
Public Function cGetRecordset(ByVal DSN As String, ByVal sqlQueryString As String, _
Optional ByVal userID As String, Optional ByVal password As String) As Object 'ADODB.Recordset
    '
    cUserID = userID
    cPassword = password
    cDSN = DSN
    cSqlQueryString = sqlQueryString
    '
    cOpenConnection
    cOpenRecordset
    '
    Set cGetRecordset = cRecordset
End Function
'
Private Function cOpenConnection()
    '
    Set cConnection = CreateObject("ADODB.Connection") 'New ADODB.Connection
    cConnection.Open cDSN, cUserID, cPassword
End Function
'
Private Function cCloseConnection()
    '
    cConnection.Close
    Set cConnection = Nothing
End Function
'
Private Function cOpenRecordset()
    '
    Set cRecordset = CreateObject("ADODB.Recordset") 'New ADODB.Recordset
    cRecordset.CursorLocation = CONST_AD_USE_CLIENT
    cRecordset.Open cSqlQueryString, cConnection, CONST_AD_OPEN_STATIC, CONST_AD_LOCK_READONLY
End Function
'
Private Function cCloseRecordset()
    '
    cRecordset.Close
    Set cRecordset = Nothing
End Function
'
Private Sub Class_Terminate()
    '
    If Not (cRecordset Is Nothing) Then Set cRecordset = Nothing
    If Not (cConnection Is Nothing) Then Set cConnection = Nothing
End Sub
'