In bautheac/pullit: Pulling financial data from Bloomberg made easy

knitr::opts_chunk$set(collapse = TRUE, eval = TRUE, echo = FALSE, comment = "#>", warning = FALSE, message = FALSE)
folder <- "literature_files"; dir.create(folder)
download.file("https://www.dropbox.com/s/htnd7o9nnkk8ng8/references.bib?dl=1", paste(folder, "references.bib", sep = "/"))
path <- here::here("development", "storethat.sqlite")

pullit together with storethat are the two workhorses for data ETL (extract, transform, load) work in the finRes suite. Install the development version with devtools::install_github("bautheac/pullit").

Using carefully selected Bloomberg datafields from BBGsymbols in tandem with @Armstrong_Rblpapi's Bloomberg interface pullit provides the R user with easy access to Bloomberg financial data for a collection of financial instruments that, at the time of writing, includes equity and equity-like securities, funds with the category encompassing any money-managing entity, as well as futures series & term structure individual contracts.

Bloomberg

An active Bloomberg connection is required to retrieve Bloomberg financial data using pullit.

library(pullit); library(lubridate)

start <- "2016-01-01"; end <- "2017-01-01"

equity

Equity data comes in three major categories in pullit, market, book and info. For a given corporation market data records stock market activity while book data records business activity in the form of financial statements commonly referred to as 'books' and info refers to qualitative information. See the fields dataset in BBGsymbols for a detailed list of the Bloomberg fields available.

market

Retrieve market data from Bloomberg for BHP Billiton (BHP US Equity), Nucor (NUE US Equity) and Rio Tinto (RIO US Equity) with:

tickers <- c("BHP US Equity", "NUE US Equity", "RIO US Equity")
equity_market <- pull_equity_market(source = "Bloomberg", tickers, start, end, verbose = F)

tickers <- c("BHP US Equity", "NUE US Equity", "RIO US Equity")
equity_market <- pull_equity_market(source = "storethat", tickers, start, end, verbose = F, file = path)
equity_market

The function returns an object that carries the retrieved financial data as well as complementary information that can be accessed using accessor methods. It includes a 'tickers' dataset that indicates the tickers for which some data have been found, a 'fields' dataset that indicates the data fields for which data has been found, a 'data' dataset that hosts the retrieved data as well as a character vector hosting the original call to the function. A get_periods() method complements the accessor methods by indicating the start and end dates between which data have been found for each ticker and data field:

• Tickers:

get_tickers(equity_market)

• Fields:

get_fields(equity_market)

The fields helper dataset from BBGsymbols allows to work with plain English descriptions of the fields in place of Bloomberg symbols:

library(BBGsymbols)
data("fields", package = "BBGsymbols")

dplyr::left_join(
  get_fields(equity_market), dplyr::select(fields, name, symbol), by = "symbol"
) %>% dplyr::select(ticker, instrument, book, field = name)

• Market data:

get_data(equity_market)

With BBGsymbols:

dplyr::left_join(
  get_data(equity_market), dplyr::select(fields, name, symbol), by = c("field" = "symbol")
) %>% dplyr::select(ticker, field = name, date, value)

• Original function call:

get_call(equity_market)

quote(pull_equity_market(source = "Bloomberg", tickers, start, end, verbose = F))

• Time period for which data is available for each ticker-field combinations:

get_periods(equity_market)

books

For financial statements data BBGsymbols replicates the Bloomberg 'financial analysis' monitor (FA \<GO>). At the time of writing, this includes balance sheet, cash flow statement, income statement as well as 'key stats' that gathers broad summary figures and 'ratios' that includes popular financial ratios.

Retrieve the corresponding data for the abovementioned corporations with:

balance sheet

equity_BS <- pull_equity_book(
  source = "Bloomberg", book = "balance sheet", tickers, start, end, verbose = F
  )

equity_BS <- pull_equity_book(source = "storethat", book = "balance sheet", tickers, start, end, verbose = F, file = path)
equity_BS

get_tickers(equity_BS)

get_fields(equity_BS) %>% head()

get_data(equity_BS) %>% tail()

cash flow statement

equity_CF <- pull_equity_book(
  source = "Bloomberg", book = "cash flow statement", tickers, start, end, verbose = F
  )

equity_CF <- pull_equity_book(source = "storethat", book = "cash flow statement", tickers, start, end, verbose = F, file = path)
equity_CF

get_tickers(equity_CF)

get_fields(equity_CF) %>% head()

get_data(equity_CF) %>% tail()

income statement

equity_IS <- pull_equity_book(
  source = "Bloomberg", book = "income statement", tickers, start, end, verbose = F
  )

equity_IS <- pull_equity_book(source = "storethat", book = "income statement", tickers, start, end, verbose = F, file = path)
equity_IS

get_tickers(equity_IS)

get_fields(equity_IS) %>% head()

get_data(equity_IS) %>% tail()

key stats

equity_KS <- pull_equity_book(
  source = "Bloomberg", book = "key stats", tickers, start, end, verbose = F
  )

equity_KS <- pull_equity_book(source = "storethat", book = "key stats", tickers, start, end, verbose = F, file = path)
equity_KS

get_tickers(equity_KS)

get_fields(equity_KS) %>% head()

get_data(equity_KS) %>% tail()

ratios

equity_R <- pull_equity_book(
  source = "Bloomberg", book = "ratios", tickers, start, end, verbose = F
  )

equity_R <- pull_equity_book(source = "storethat", book = "ratios", tickers, start, end, verbose = F, file = path)
equity_R

get_tickers(equity_R)

get_fields(equity_R) %>% head()

get_data(equity_R) %>% tail()

info

'info' encompasses a range of contemporaneous qualitative information including, but not limited to, firm's name, security type, exchange where the security trades, country of incorporation, etc.

Retrieve the corresponding data for the abovementioned corporations with:

equity_info <- pull_equity_info(source = "Bloomberg", tickers, verbose = F)

equity_info <- pull_equity_info(source = "storethat", tickers, verbose = F, file = path)
equity_info

get_info(equity_info) %>% head()

get_fields(equity_info) %>% tail()

fund

Fund data only comes in two categories, market and info. For a given fund market data records stock market activity while info data records contemporaneous qualitative information that includes a wide range of fund characteristics. Market historical data as well as contemporaneous qualitative data not only allow for an indepth assessment of the fund performance but potentially also the mapping of the performance to fund characteristics. See the fields dataset in BBGsymbols for a detailed list of the Bloomberg datafields available.

market

Retrieve market data from Bloomberg for an exchange traded note (UGAZ US Equity) that leverages, long exposure to the US natural gas market:

ticker <- "UGAZ US Equity"

fund_market <- pull_fund_market(source = "Bloomberg", ticker, start, end, verbose = F)

ticker <- "UGAZ US Equity"

fund_market <- pull_fund_market(source = "storethat", ticker, start, end, verbose = F, file = path)
fund_market

get_tickers(fund_market)

get_fields(fund_market) %>% head()

get_data(fund_market)%>% tail()

info

Retrieve the corresponding qualitative data for the abovementioned funds with:

fund_info <- pull_fund_info(source = "Bloomberg", ticker, verbose = F)

fund_info <- pull_fund_info(source = "storethat", ticker, verbose = F, file = path)
fund_info

get_info(fund_info) %>% head()

get_fields(fund_info) %>% tail()

futures

Futures data comes in three major categories in pullit, market, CFTC and info. For a given futures series market data records futures market price activity while CFTC data records market positions. 'info' on the other hand records contemporaneous qualitative information for the corresponding futures series, including but not limited to series name, term structure length, contract size, contract unit, etc. See the fields dataset in BBGsymbols for a detailed list of the Bloomberg datafields available.

market

pullit helps to retrieve both term structure as well as aggregated market data for futures. Term structure data records market activity for individual futures term structure contracts while aggregated data records market activity measures that are aggregated over the whole term structure for a particular futures series.

term structure

Retrieve market data from Bloomberg for the five first term structure contracts on the US traded corn (C A Comdty), crude oil (CLA Comdty) and silver (SIA Comdty) futures series using:

tickers <- c("C A Comdty", "EDA Comdty", "ESA Index")

futures_TS <- pull_futures_market(
  source = "Bloomberg", type = "term structure", active_contract_tickers = tickers,
  start, end, TS_positions = 1L:5L, roll_type = "A", roll_days = 0L,
  roll_months = 0L, roll_adjustment = "N", verbose = F
  )

tickers <- c("C A Comdty", "CLA Comdty", "SIA Comdty")

futures_TS <- pull_futures_market(source = "storethat", type = "term structure", active_contract_tickers = tickers, 
                                  start, end, TS_positions = 1L:5L, roll_type = "A", roll_days = 0L, roll_months = 0L, 
                                  roll_adjustment = "N", verbose = F, file = path)
futures_TS

get_active_contract_tickers(futures_TS)

BBGsymbols helps with futures as well with the tickers_futures dataset that provides information for a number of popular series. The exchanges dataset from fewISOS further provides information on the various exchange markets where these are traded.

data("tickers_futures", package = "BBGsymbols")
data("exchanges", package = "fewISOs")
dplyr::left_join(
  get_active_contract_tickers(futures_TS), 
  dplyr::select(tickers_futures, ticker, name, sector, subsector, MIC), 
  by = c("active contract ticker" = "ticker")
  ) %>% 
  dplyr::left_join(
    dplyr::select(exchanges, MIC, exchange = name), 
    by = "MIC"
  ) %>% dplyr::select(-MIC)

get_term_structure_tickers(futures_TS) %>% head()

dplyr::left_join(
  get_term_structure_tickers(futures_TS), dplyr::select(tickers_futures, ticker, name), 
  by = c("active contract ticker" = "ticker")
  ) %>% dplyr::select(name, dplyr::everything()) %>% head()

get_fields(futures_TS) %>% tail()

get_data(futures_TS) %>% head()

With BBGsymbols:

dplyr::left_join(
  get_data(futures_TS),
  dplyr::select(get_term_structure_tickers(futures_TS), `active contract ticker`, ticker, `TS position`),
  by = "ticker"
  ) %>%
  dplyr::left_join(
    dplyr::select(tickers_futures, ticker, name), 
    by = c("active contract ticker" = "ticker")
  ) %>%
  dplyr::rename(symbol = field) %>%
  dplyr::left_join(dplyr::select(fields, symbol, field = name), by = "symbol") %>% 
  dplyr::select(asset = name, `TS position`, field, date, value) %>% head()

aggregate

Bloomberg further provides data aggregated over the entire term structure for a given futures series where variable values are summed up for all term structure contracts traded at a given time. This data can be accessed using pullit with:

futures_agg <- pull_futures_market(source = "Bloomberg", type = "aggregate", active_contract_tickers = tickers,
                                  start, end, verbose = F)

futures_agg <- pull_futures_market(source = "storethat", type = "aggregate", active_contract_tickers = tickers,
                                  start, end, verbose = F, file = path)
futures_agg

get_active_contract_tickers(futures_agg)

get_fields(futures_agg)

get_data(futures_agg) %>% head()

CFTC

pullit further helps to retrieve CFTC futures market position data from Bloomberg. The Commodity Futures Trading Commission (CFTC) publishes the Commitments of Traders (COT) reports to help the public understand market dynamics. Specifically, the COT reports provide a breakdown of each Tuesday’s open interest for futures and options on futures markets in which 20 or more traders hold positions equal to or above the reporting levels established by the CFTC. See the fields and tickers_cftc datasets in BBGsymbols for details.

Retrieve the corresponding futures market position data with:

futures_CFTC <- pull_futures_CFTC(source = "Bloomberg", active_contract_tickers = tickers, 
                                  start, end, verbose = F)

futures_CFTC <- pull_futures_CFTC(source = "storethat", active_contract_tickers = tickers, 
                                  start, end, verbose = F, file = path)
futures_CFTC

get_active_contract_tickers(futures_CFTC)

get_CFTC_tickers(futures_CFTC) %>% head()

get_data(futures_CFTC) %>% tail()

With BBGsymbols:

data("tickers_cftc", package = "BBGsymbols")

dplyr::left_join(
    get_data(futures_CFTC),
    dplyr::select(tickers_futures, ticker, name), 
    by = c("active contract ticker" = "ticker")
  ) %>%
  dplyr::select(asset = name, ticker, date, value) %>%
  dplyr::left_join(
    dplyr::select(tickers_cftc, ticker, format, underlying, unit, participant, position),
    by = "ticker"
    ) %>%
  dplyr::select(-ticker) %>% 
  dplyr::relocate(c("date", "value"), .after = dplyr::last_col()) %>%
  dplyr::arrange(asset, format, underlying, unit, participant, date, position) %>%
  head()

info

'info' encompasses a range of contemporaneous qualitative information on the underlying futures series including, but not limited to, name for the series' underlying, trading exchange, term structure length, contract size, etc.

Retrieve the corresponding data for the abovementioned futures series with:

futures_info <- pull_futures_info(source = "Bloomberg", tickers, verbose = F)

futures_info <- pull_futures_info(source = "storethat", tickers, verbose = F, file = path)
futures_info

get_info(futures_info) %>% tail()

get_fields(futures_info) %>% head()

index

Index data, similar to fund data, comes in two categories, market and info. For a given index market data records historical time series values while info data records contemporaneous qualitative information that includes a wide range of index characteristics. See the fields dataset in BBGsymbols for a detailed list of the Bloomberg datafields available.

market

Retrieve market data from Bloomberg for the S&P 500 total return index (SPXT Index) with:

tickers <- "SPXT Index"

index_market <- pull_index_market(source = "Bloomberg", tickers, start, end, verbose = F)

tickers <- "SPXT Index"

index_market <- pull_index_market(source = "storethat", tickers, start, end, verbose = F, file = path)
index_market

get_tickers(index_market)

get_fields(index_market) %>% head()

get_data(index_market)%>% tail()

info

Retrieve the corresponding qualitative data for the abovementioned funds with:

index_info <- pull_index_info(source = "Bloomberg", tickers, verbose = F)
index_info

index_info <- pull_index_info(source = "storethat", tickers, verbose = F, file = path)
index_info

get_info(index_info) %>% tail()

get_fields(index_info) %>% head()

storethat

All the objects above can be stored in a bespoke database for later off-Bloomberg consumption. The storethat package makes the process seamless by providing bespoke storing methods:

library(storethat)

db_create()

db_store(object = futures_TS, file = "~/storethat.sqlite", verbose = F)
db_store(object = fund_market, file = "~/storethat.sqlite", verbose = F)

The functions above can also be used for retrieving data from a storethat database; this is done by switching the source parameter from "Bloomberg" to "storethat". Function parameters are identical for both data sources and providing the path to the database as a string in the file parameter. If omitted a pop-up window allows to browse the file system and select the database file. The other function parameters are identical for both data sources and the returned objects are identical in all aspects other than the source of the data they contain:

ticker <- "C A Comdty"
futures_TS <- pull_futures_market(
  source = "storethat", type = "term structure", active_contract_tickers = ticker,
  start, end, TS_positions = 1L:5L, roll_type = "A", roll_days = 0L, roll_months = 0L,
  roll_adjustment = "N", verbose = F
  )

ticker <- "SPY US Equity"
fund_market <- pull_fund_market(source = "storethat", tickers, start, end, verbose = F)

path = here::here("development", "storethat.sqlite")
ticker <- "C A Comdty"
futures_TS <- pull_futures_market(source = "storethat", type = "term structure", active_contract_tickers = ticker,
                                  start, end, TS_positions = 1L:5L, roll_type = "A", roll_days = 0L, roll_months = 0L,
                                  roll_adjustment = "N", verbose = F, file = path)

ticker <- "UGAZ US Equity"
fund_market <- pull_fund_market(source = "storethat", ticker, start, end, verbose = F, file = path)

futures_TS

fund_market

Updating a storethat database is equally straightforward with pullit. Update the whole equity content of the database with:

storethat_update(instrument = "equity", verbose = F)

More refined updates are also allowed:

storethat_update(instrument = "equity", book = "market")

plotit

The plotit package, also part of the finRes suite, provides plot methods for some pullit data objects including, at the time of writing, futures term structure (FuturesTS) and fund market (FundMarket) objects.

futures term structure

Plot a futures series term structure dynamics with:

library(plotit)

plot(object = futures_TS, ticker = "C A Comdty")

fund market

Plot historical fund performance with:

plot(object = fund_market, ticker = "UGAZ US Equity")

references

bautheac/pullit documentation built on June 7, 2021, 12:11 p.m.