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, Eddelbuettel, and Laing (2021)’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.
An active Bloomberg connection is required to retrieve Bloomberg financial data using pullit.
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.
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)
#> S4 object of class EquityMarket
#>
#> Slots inlude
#> tickers: access with get_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
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:
get_tickers(equity_market)
#> ticker
#> 1: BHP US Equity
#> 2: NUE US Equity
#> 3: RIO US Equity
get_fields(equity_market)
#> ticker instrument book symbol
#> 1: BHP US Equity equity market CUR_MKT_CAP
#> 2: BHP US Equity equity market EQY_SH_OUT
#> 3: BHP US Equity equity market PX_ASK
#> 4: BHP US Equity equity market PX_BID
#> 5: BHP US Equity equity market PX_HIGH
#> 6: BHP US Equity equity market PX_LAST
#> 7: BHP US Equity equity market PX_LOW
#> 8: BHP US Equity equity market PX_MID
#> 9: BHP US Equity equity market PX_OPEN
#> 10: BHP US Equity equity market PX_VOLUME
#> 11: NUE US Equity equity market CUR_MKT_CAP
#> 12: NUE US Equity equity market EQY_SH_OUT
#> 13: NUE US Equity equity market PX_ASK
#> 14: NUE US Equity equity market PX_BID
#> 15: NUE US Equity equity market PX_HIGH
#> 16: NUE US Equity equity market PX_LAST
#> 17: NUE US Equity equity market PX_LOW
#> 18: NUE US Equity equity market PX_MID
#> 19: NUE US Equity equity market PX_OPEN
#> 20: NUE US Equity equity market PX_VOLUME
#> 21: RIO US Equity equity market CUR_MKT_CAP
#> 22: RIO US Equity equity market EQY_SH_OUT
#> 23: RIO US Equity equity market PX_ASK
#> 24: RIO US Equity equity market PX_BID
#> 25: RIO US Equity equity market PX_HIGH
#> 26: RIO US Equity equity market PX_LAST
#> 27: RIO US Equity equity market PX_LOW
#> 28: RIO US Equity equity market PX_MID
#> 29: RIO US Equity equity market PX_OPEN
#> 30: RIO US Equity equity market PX_VOLUME
#> ticker instrument book symbol
The fields
helper dataset from BBGsymbols allows to work with plain English descriptions of the fields in place of Bloomberg symbols:
#> ticker instrument book field
#> 1: BHP US Equity equity market market capitalization
#> 2: BHP US Equity equity market market capitalization
#> 3: BHP US Equity equity market # shares outstanding
#> 4: BHP US Equity equity market # shares outstanding
#> 5: BHP US Equity equity market ask
#> ---
#> 107: RIO US Equity equity market open price
#> 108: RIO US Equity equity market open price
#> 109: RIO US Equity equity market volume
#> 110: RIO US Equity equity market volume
#> 111: RIO US Equity equity market volume
get_data(equity_market)
#> ticker field date value
#> 1: BHP US Equity CUR_MKT_CAP 2016-01-04 63751.03
#> 2: BHP US Equity CUR_MKT_CAP 2016-01-05 63668.93
#> 3: BHP US Equity CUR_MKT_CAP 2016-01-06 60049.91
#> 4: BHP US Equity CUR_MKT_CAP 2016-01-07 56812.11
#> 5: BHP US Equity CUR_MKT_CAP 2016-01-08 55249.09
#> ---
#> 7556: RIO US Equity PX_VOLUME 2016-12-23 1000085.00
#> 7557: RIO US Equity PX_VOLUME 2016-12-27 868238.00
#> 7558: RIO US Equity PX_VOLUME 2016-12-28 1923936.00
#> 7559: RIO US Equity PX_VOLUME 2016-12-29 1766836.00
#> 7560: RIO US Equity PX_VOLUME 2016-12-30 2868697.00
With BBGsymbols:
#> ticker field date value
#> 1: BHP US Equity market capitalization 2016-01-04 63751.03
#> 2: BHP US Equity market capitalization 2016-01-04 63751.03
#> 3: BHP US Equity market capitalization 2016-01-05 63668.93
#> 4: BHP US Equity market capitalization 2016-01-05 63668.93
#> 5: BHP US Equity market capitalization 2016-01-06 60049.91
#> ---
#> 27968: RIO US Equity volume 2016-12-29 1766836.00
#> 27969: RIO US Equity volume 2016-12-29 1766836.00
#> 27970: RIO US Equity volume 2016-12-30 2868697.00
#> 27971: RIO US Equity volume 2016-12-30 2868697.00
#> 27972: RIO US Equity volume 2016-12-30 2868697.00
get_call(equity_market)
#> pull_equity_market(source = "Bloomberg", tickers, start, end,
#> verbose = F)
get_periods(equity_market)
#> ticker field start end
#> 1: BHP US Equity CUR_MKT_CAP 2016-01-04 2016-12-30
#> 2: BHP US Equity EQY_SH_OUT 2016-01-04 2016-12-30
#> 3: BHP US Equity PX_ASK 2016-01-04 2016-12-30
#> 4: BHP US Equity PX_BID 2016-01-04 2016-12-30
#> 5: BHP US Equity PX_HIGH 2016-01-04 2016-12-30
#> 6: BHP US Equity PX_LAST 2016-01-04 2016-12-30
#> 7: BHP US Equity PX_LOW 2016-01-04 2016-12-30
#> 8: BHP US Equity PX_MID 2016-01-04 2016-12-30
#> 9: BHP US Equity PX_OPEN 2016-01-04 2016-12-30
#> 10: BHP US Equity PX_VOLUME 2016-01-04 2016-12-30
#> 11: NUE US Equity CUR_MKT_CAP 2016-01-04 2016-12-30
#> 12: NUE US Equity EQY_SH_OUT 2016-01-04 2016-12-30
#> 13: NUE US Equity PX_ASK 2016-01-04 2016-12-30
#> 14: NUE US Equity PX_BID 2016-01-04 2016-12-30
#> 15: NUE US Equity PX_HIGH 2016-01-04 2016-12-30
#> 16: NUE US Equity PX_LAST 2016-01-04 2016-12-30
#> 17: NUE US Equity PX_LOW 2016-01-04 2016-12-30
#> 18: NUE US Equity PX_MID 2016-01-04 2016-12-30
#> 19: NUE US Equity PX_OPEN 2016-01-04 2016-12-30
#> 20: NUE US Equity PX_VOLUME 2016-01-04 2016-12-30
#> 21: RIO US Equity CUR_MKT_CAP 2016-01-04 2016-12-30
#> 22: RIO US Equity EQY_SH_OUT 2016-01-04 2016-12-30
#> 23: RIO US Equity PX_ASK 2016-01-04 2016-12-30
#> 24: RIO US Equity PX_BID 2016-01-04 2016-12-30
#> 25: RIO US Equity PX_HIGH 2016-01-04 2016-12-30
#> 26: RIO US Equity PX_LAST 2016-01-04 2016-12-30
#> 27: RIO US Equity PX_LOW 2016-01-04 2016-12-30
#> 28: RIO US Equity PX_MID 2016-01-04 2016-12-30
#> 29: RIO US Equity PX_OPEN 2016-01-04 2016-12-30
#> 30: RIO US Equity PX_VOLUME 2016-01-04 2016-12-30
#> ticker field start end
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:
equity_BS <- pull_equity_book(
source = "Bloomberg", book = "balance sheet", tickers, start, end, verbose = F
)
#> S4 object of class EquityBS
#>
#> Slots inlude
#> tickers: access with get_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_tickers(equity_BS)
#> ticker
#> 1: BHP US Equity
#> 2: NUE US Equity
#> 3: RIO US Equity
get_fields(equity_BS) %>% head()
#> ticker instrument book type subtype section
#> 1: BHP US Equity equity balance sheet as reported <NA> assets
#> 2: BHP US Equity equity balance sheet as reported <NA> assets
#> 3: BHP US Equity equity balance sheet as reported <NA> assets
#> 4: BHP US Equity equity balance sheet as reported <NA> assets
#> 5: BHP US Equity equity balance sheet as reported <NA> assets
#> 6: BHP US Equity equity balance sheet as reported <NA> assets
#> subsection symbol
#> 1: current ARD_CASH_AND_EQUIVALENTS
#> 2: current ARD_INVENTORY
#> 3: current ARD_OTHER_CURRENT_ASSETS
#> 4: current ARD_TOTAL_CUR_ASSETS
#> 5: long term ARD_OTHER_NONCURRENT_ASSET
#> 6: long term ARD_PROPERTY_PLANT_EQUIP_NET
get_data(equity_BS) %>% tail()
#> ticker field date value
#> 1: RIO US Equity TCE_RATIO 2016-06-30 38.5221
#> 2: RIO US Equity TCE_RATIO 2016-12-31 41.7184
#> 3: RIO US Equity TOT_LIAB_AND_EQY 2016-06-30 90388.0000
#> 4: RIO US Equity TOT_LIAB_AND_EQY 2016-12-31 89263.0000
#> 5: RIO US Equity TOTAL_EQUITY 2016-06-30 44060.0000
#> 6: RIO US Equity TOTAL_EQUITY 2016-12-31 45730.0000
equity_CF <- pull_equity_book(
source = "Bloomberg", book = "cash flow statement", tickers, start, end, verbose = F
)
#> S4 object of class EquityCF
#>
#> Slots inlude
#> tickers: access with get_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_tickers(equity_CF)
#> ticker
#> 1: BHP US Equity
#> 2: NUE US Equity
#> 3: RIO US Equity
get_fields(equity_CF) %>% head()
#> ticker instrument book type subtype section
#> 1: BHP US Equity equity cash flow statement as reported <NA> financing
#> 2: BHP US Equity equity cash flow statement as reported <NA> financing
#> 3: BHP US Equity equity cash flow statement as reported <NA> financing
#> 4: BHP US Equity equity cash flow statement as reported <NA> financing
#> 5: BHP US Equity equity cash flow statement as reported <NA> financing
#> 6: BHP US Equity equity cash flow statement as reported <NA> financing
#> subsection symbol
#> 1: <NA> ARD_CASH_CASH_EQUIV_BEG_OF_PER
#> 2: <NA> ARD_CASH_CASH_EQUIV_END_OF_PER
#> 3: <NA> ARD_CASH_PAID_FOR_INTEREST
#> 4: <NA> ARD_CASH_PAID_FOR_TAXES
#> 5: <NA> ARD_DIVIDEND_PD
#> 6: <NA> ARD_NET_CHANGE_IN_CASH
get_data(equity_CF) %>% tail()
#> ticker field date value
#> 1: RIO US Equity OTHER_INVESTING_ACT_DETAILED 2016-06-30 110
#> 2: RIO US Equity OTHER_INVESTING_ACT_DETAILED 2016-12-31 -110
#> 3: RIO US Equity PROC_FR_REPAYMNTS_BOR_DETAILED 2016-06-30 -1914
#> 4: RIO US Equity PROC_FR_REPAYMNTS_BOR_DETAILED 2016-12-31 -3034
#> 5: RIO US Equity PROC_FR_REPURCH_EQTY_DETAILED 2016-06-30 0
#> 6: RIO US Equity PROC_FR_REPURCH_EQTY_DETAILED 2016-12-31 0
equity_IS <- pull_equity_book(
source = "Bloomberg", book = "income statement", tickers, start, end, verbose = F
)
#> S4 object of class EquityIS
#>
#> Slots inlude
#> tickers: access with get_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_tickers(equity_IS)
#> ticker
#> 1: BHP US Equity
#> 2: NUE US Equity
#> 3: RIO US Equity
get_fields(equity_IS) %>% head()
#> ticker instrument book type subtype section
#> 1: BHP US Equity equity income statement adjusted <NA> <NA>
#> 2: BHP US Equity equity income statement adjusted <NA> <NA>
#> 3: BHP US Equity equity income statement adjusted <NA> <NA>
#> 4: BHP US Equity equity income statement adjusted <NA> <NA>
#> 5: BHP US Equity equity income statement adjusted <NA> <NA>
#> 6: BHP US Equity equity income statement adjusted <NA> <NA>
#> subsection symbol
#> 1: <NA> ACCOUNTING_STANDARD
#> 2: <NA> ACTUAL_SALES_PER_EMPL
#> 3: <NA> EARN_FOR_COMMON
#> 4: <NA> EBIT
#> 5: <NA> EBITDA
#> 6: <NA> EBITDA_MARGIN
get_data(equity_IS) %>% tail()
#> ticker field date value
#> 1: RIO US Equity SALES_REV_TURN 2016-06-30 15500
#> 2: RIO US Equity SALES_REV_TURN 2016-12-31 18281
#> 3: RIO US Equity XO_GL_NET_OF_TAX 2016-06-30 0
#> 4: RIO US Equity XO_GL_NET_OF_TAX 2016-06-30 0
#> 5: RIO US Equity XO_GL_NET_OF_TAX 2016-12-31 0
#> 6: RIO US Equity XO_GL_NET_OF_TAX 2016-12-31 0
equity_KS <- pull_equity_book(
source = "Bloomberg", book = "key stats", tickers, start, end, verbose = F
)
#> S4 object of class EquityKS
#>
#> Slots inlude
#> tickers: access with get_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_tickers(equity_KS)
#> ticker
#> 1: BHP US Equity
#> 2: NUE US Equity
#> 3: RIO US Equity
get_fields(equity_KS) %>% head()
#> ticker instrument book type subtype section
#> 1: BHP US Equity equity key stats adjusted highlights <NA> <NA>
#> 2: BHP US Equity equity key stats adjusted highlights <NA> <NA>
#> 3: BHP US Equity equity key stats adjusted highlights <NA> <NA>
#> 4: BHP US Equity equity key stats adjusted highlights <NA> <NA>
#> 5: BHP US Equity equity key stats adjusted highlights <NA> <NA>
#> 6: BHP US Equity equity key stats adjusted highlights <NA> <NA>
#> subsection symbol
#> 1: <NA> CAPITAL_EXPEND
#> 2: <NA> CF_CASH_FROM_OPER
#> 3: <NA> CF_FREE_CASH_FLOW
#> 4: <NA> EARN_FOR_COMMON
#> 5: <NA> EBITDA
#> 6: <NA> ENTERPRISE_VALUE
get_data(equity_KS) %>% tail()
#> ticker field date value
#> 1: RIO US Equity TOTAL_EQUITY 2016-12-31 45730.000
#> 2: RIO US Equity TRAIL_12M_CASH_FLOW_FIRM 2016-12-31 9312.076
#> 3: RIO US Equity TRAIL_12M_EBITDA 2016-06-30 7265.000
#> 4: RIO US Equity TRAIL_12M_FREE_CASH_FLOW 2016-06-30 4659.000
#> 5: RIO US Equity TRAIL_12M_NET_SALES 2016-06-30 32349.000
#> 6: RIO US Equity TRAIL_12M_OPER_INC 2016-06-30 2644.000
equity_R <- pull_equity_book(
source = "Bloomberg", book = "ratios", tickers, start, end, verbose = F
)
#> S4 object of class EquityRatios
#>
#> Slots inlude
#> tickers: access with get_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_tickers(equity_R)
#> ticker
#> 1: BHP US Equity
#> 2: NUE US Equity
#> 3: RIO US Equity
get_fields(equity_R) %>% head()
#> ticker instrument book type subtype section subsection
#> 1: BHP US Equity equity ratios credit <NA> <NA> <NA>
#> 2: BHP US Equity equity ratios credit <NA> <NA> <NA>
#> 3: BHP US Equity equity ratios credit <NA> <NA> <NA>
#> 4: BHP US Equity equity ratios credit <NA> <NA> <NA>
#> 5: BHP US Equity equity ratios credit <NA> <NA> <NA>
#> 6: BHP US Equity equity ratios credit <NA> <NA> <NA>
#> symbol
#> 1: CF_ACT_CASH_PAID_FOR_INT_DEBT
#> 2: COM_EQY_TO_TOT_ASSET
#> 3: EBIT
#> 4: EBIT_TO_CASH_INTEREST_PAID
#> 5: EBITDA
#> 6: EBITDA_AFT_CAPEX_TO_CASH_INT_PD
get_data(equity_R) %>% tail()
#> ticker field date value
#> 1: RIO US Equity WORK_CAP_GROWTH 2016-06-30 -8.4870
#> 2: RIO US Equity WORK_CAP_GROWTH 2016-12-31 5.3548
#> 3: RIO US Equity WORKING_CAPITAL_5_YEAR_GROWTH 2016-06-30 -6.1248
#> 4: RIO US Equity WORKING_CAPITAL_5_YEAR_GROWTH 2016-12-31 -4.0371
#> 5: RIO US Equity WORKING_CAPITAL_SEQ_GROWTH 2016-06-30 6.6889
#> 6: RIO US Equity WORKING_CAPITAL_SEQ_GROWTH 2016-12-31 -1.2504
‘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)
#> S4 object of class EquityInfo
#>
#> Slots inlude
#> info: access with get_info()
#> fields: access with get_fields()
#> call: access with get_call()
get_info(equity_info) %>% head()
#> # A tibble: 6 x 3
#> ticker symbol value
#> <chr> <chr> <chr>
#> 1 BHP US Equity LONG_COMP_NAME BHP Group Ltd
#> 2 BHP US Equity SECURITY_TYP ADR
#> 3 BHP US Equity ID_MIC_PRIM_EXCH XNYS
#> 4 BHP US Equity CNTRY_OF_INCORPORATION AU
#> 5 BHP US Equity CRNCY USD
#> 6 BHP US Equity ID_BB_GLOBAL BBG000BD5NZ4
get_fields(equity_info) %>% tail()
#> ticker instrument book symbol
#> 1: RIO US Equity equity info ID_BB_GLOBAL
#> 2: RIO US Equity equity info GICS_SECTOR
#> 3: RIO US Equity equity info GICS_INDUSTRY_GROUP
#> 4: RIO US Equity equity info GICS_INDUSTRY
#> 5: RIO US Equity equity info GICS_SUB_INDUSTRY
#> 6: RIO US Equity equity info CIE_DES
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.
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)
#> S4 object of class FundMarket
#>
#> Slots inlude
#> tickers: access with get_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_tickers(fund_market)
#> ticker
#> 1: UGAZ US Equity
get_fields(fund_market) %>% head()
#> ticker instrument book symbol
#> 1: UGAZ US Equity fund market CEF_PCT_PREM
#> 2: UGAZ US Equity fund market CUR_MKT_CAP
#> 3: UGAZ US Equity fund market EQY_SH_OUT
#> 4: UGAZ US Equity fund market FUND_FLOW
#> 5: UGAZ US Equity fund market FUND_NET_ASSET_VAL
#> 6: UGAZ US Equity fund market FUND_TOTAL_ASSETS
get_data(fund_market)%>% tail()
#> ticker field date value
#> 1: UGAZ US Equity PX_VOLUME 2016-12-22 715680
#> 2: UGAZ US Equity PX_VOLUME 2016-12-23 548891
#> 3: UGAZ US Equity PX_VOLUME 2016-12-27 391579
#> 4: UGAZ US Equity PX_VOLUME 2016-12-28 395420
#> 5: UGAZ US Equity PX_VOLUME 2016-12-29 459228
#> 6: UGAZ US Equity PX_VOLUME 2016-12-30 255499
Retrieve the corresponding qualitative data for the abovementioned funds with:
fund_info <- pull_fund_info(source = "Bloomberg", ticker, verbose = F)
#> S4 object of class FundInfo
#>
#> Slots inlude
#> info: access with get_info()
#> fields: access with get_fields()
#> call: access with get_call()
get_info(fund_info) %>% head()
#> # A tibble: 6 x 3
#> ticker symbol value
#> <chr> <chr> <chr>
#> 1 UGAZ US Equity LONG_COMP_NAME "VelocityShares 3x Long Natural Gas ETN"
#> 2 UGAZ US Equity LONG_PARENT_COMP_NAME "Credit Suisse AG VelocityShares/Nassau"
#> 3 UGAZ US Equity FUND_MASTER_NAME ""
#> 4 UGAZ US Equity ID_MIC_PRIM_EXCH "ARCX"
#> 5 UGAZ US Equity CRNCY "USD"
#> 6 UGAZ US Equity FD_INITIAL_VAL "50"
get_fields(fund_info) %>% tail()
#> ticker instrument book symbol
#> 1: UGAZ US Equity fund info FUND_HAS_COMPLEX_FRNT_SCHED
#> 2: UGAZ US Equity fund info FUND_EXPENSE_RATIO
#> 3: UGAZ US Equity fund info FUND_ADMIN_EXP
#> 4: UGAZ US Equity fund info FUND_TOTAL_EXP
#> 5: UGAZ US Equity fund info FUND_EXP_ASOF_DT
#> 6: UGAZ US Equity fund info FUND_HURDLE_RATE
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.
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.
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:
#> S4 object of class FuturesTS
#>
#> Slots inlude
#> active_contract_tickers: access with get_active_contract_tickers()
#> term_structure_tickers: access with get_term_structure_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_active_contract_tickers(futures_TS)
#> active contract ticker
#> 1: C A Comdty
#> 2: CLA Comdty
#> 3: SIA Comdty
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.
#> active contract ticker name sector subsector
#> 1: C A Comdty Corn-#2 yellow agriculturals grains
#> 2: CLA Comdty Crude oil-WTI energy petroleum
#> 3: SIA Comdty Silver metals precious
#> exchange
#> 1: chicago board of trade
#> 2: new york mercantile exchange
#> 3: commodities exchange center
get_term_structure_tickers(futures_TS) %>% head()
#> active contract ticker ticker TS position roll type symbol
#> 1: C A Comdty C 1 A:00_0_N Comdty 1 A
#> 2: C A Comdty C 2 A:00_0_N Comdty 2 A
#> 3: C A Comdty C 3 A:00_0_N Comdty 3 A
#> 4: C A Comdty C 4 A:00_0_N Comdty 4 A
#> 5: C A Comdty C 5 A:00_0_N Comdty 5 A
#> 6: CLA Comdty CL1 A:00_0_N Comdty 1 A
#> roll days roll months roll adjustment symbol
#> 1: 0 0 N
#> 2: 0 0 N
#> 3: 0 0 N
#> 4: 0 0 N
#> 5: 0 0 N
#> 6: 0 0 N
get_fields(futures_TS) %>% tail()
#> active contract ticker ticker instrument book type
#> 1: SIA Comdty SI5 A:00_0_N Comdty futures market term structure
#> 2: SIA Comdty SI5 A:00_0_N Comdty futures market term structure
#> 3: SIA Comdty SI5 A:00_0_N Comdty futures market term structure
#> 4: SIA Comdty SI5 A:00_0_N Comdty futures market term structure
#> 5: SIA Comdty SI5 A:00_0_N Comdty futures market term structure
#> 6: SIA Comdty SI5 A:00_0_N Comdty futures market term structure
#> symbol
#> 1: PX_BID
#> 2: PX_HIGH
#> 3: PX_LAST
#> 4: PX_LOW
#> 5: PX_OPEN
#> 6: PX_VOLUME
get_data(futures_TS) %>% head()
#> ticker field date value
#> 1: C 1 A:00_0_N Comdty OPEN_INT 2016-01-04 727298
#> 2: C 1 A:00_0_N Comdty OPEN_INT 2016-01-05 730600
#> 3: C 1 A:00_0_N Comdty OPEN_INT 2016-01-06 727995
#> 4: C 1 A:00_0_N Comdty OPEN_INT 2016-01-07 727244
#> 5: C 1 A:00_0_N Comdty OPEN_INT 2016-01-08 719406
#> 6: C 1 A:00_0_N Comdty OPEN_INT 2016-01-11 725476
With BBGsymbols:
#> asset TS position field date value
#> 1: Corn-#2 yellow 1 open interest 2016-01-04 727298
#> 2: Corn-#2 yellow 1 open interest 2016-01-05 730600
#> 3: Corn-#2 yellow 1 open interest 2016-01-06 727995
#> 4: Corn-#2 yellow 1 open interest 2016-01-07 727244
#> 5: Corn-#2 yellow 1 open interest 2016-01-08 719406
#> 6: Corn-#2 yellow 1 open interest 2016-01-11 725476
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)
#> S4 object of class FuturesAggregate
#>
#> Slots inlude
#> active_contract_tickers: access with get_active_contract_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_active_contract_tickers(futures_agg)
#> ticker
#> 1: C A Comdty
#> 2: CLA Comdty
#> 3: SIA Comdty
get_fields(futures_agg)
#> ticker instrument book type symbol
#> 1: C A Comdty futures market aggregate FUT_AGGTE_OPEN_INT
#> 2: C A Comdty futures market aggregate FUT_AGGTE_VOL
#> 3: CLA Comdty futures market aggregate FUT_AGGTE_OPEN_INT
#> 4: CLA Comdty futures market aggregate FUT_AGGTE_VOL
#> 5: SIA Comdty futures market aggregate FUT_AGGTE_OPEN_INT
#> 6: SIA Comdty futures market aggregate FUT_AGGTE_VOL
get_data(futures_agg) %>% head()
#> ticker field date value
#> 1: C A Comdty FUT_AGGTE_OPEN_INT 2016-01-04 1350186
#> 2: C A Comdty FUT_AGGTE_OPEN_INT 2016-01-05 1356517
#> 3: C A Comdty FUT_AGGTE_OPEN_INT 2016-01-06 1358585
#> 4: C A Comdty FUT_AGGTE_OPEN_INT 2016-01-07 1364986
#> 5: C A Comdty FUT_AGGTE_OPEN_INT 2016-01-08 1373861
#> 6: C A Comdty FUT_AGGTE_OPEN_INT 2016-01-11 1393414
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:
#> S4 object of class FuturesCFTC
#>
#> Slots inlude
#> active_contract_tickers: access with get_active_contract_tickers()
#> cftc_tickers: access with get_CFTC_tickers()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_active_contract_tickers(futures_CFTC)
#> ticker
#> 1: C A Comdty
#> 2: CLA Comdty
#> 3: SIA Comdty
get_CFTC_tickers(futures_CFTC) %>% head()
#> active contract ticker ticker
#> 1: C A Comdty CBT1CCOL Index
#> 2: C A Comdty CBT1CCON Index
#> 3: C A Comdty CBT1CCOS Index
#> 4: C A Comdty CBT1CNCL Index
#> 5: C A Comdty CBT1CNCN Index
#> 6: C A Comdty CBT1CNCP Index
get_data(futures_CFTC) %>% tail()
#> active contract ticker ticker field date value
#> 1: SIA Comdty CMXOSTTS Index PX_LAST 2016-11-22 127
#> 2: SIA Comdty CMXOSTTS Index PX_LAST 2016-11-29 121
#> 3: SIA Comdty CMXOSTTS Index PX_LAST 2016-12-06 127
#> 4: SIA Comdty CMXOSTTS Index PX_LAST 2016-12-13 127
#> 5: SIA Comdty CMXOSTTS Index PX_LAST 2016-12-20 128
#> 6: SIA Comdty CMXOSTTS Index PX_LAST 2016-12-27 123
With BBGsymbols:
#> asset format underlying unit participant
#> 1: Corn-#2 yellow disaggregated futures & options contracts managed money
#> 2: Corn-#2 yellow disaggregated futures & options contracts managed money
#> 3: Corn-#2 yellow disaggregated futures & options contracts managed money
#> 4: Corn-#2 yellow disaggregated futures & options contracts managed money
#> 5: Corn-#2 yellow disaggregated futures & options contracts managed money
#> 6: Corn-#2 yellow disaggregated futures & options contracts managed money
#> position date value
#> 1: long 2016-01-05 156263
#> 2: net 2016-01-05 -161988
#> 3: short 2016-01-05 318251
#> 4: spreading 2016-01-05 117626
#> 5: long 2016-01-12 153725
#> 6: net 2016-01-12 -186831
‘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:
#> S4 object of class FuturesInfo
#>
#> Slots inlude
#> info: access with get_info()
#> fields: access with get_fields()
#> call: access with get_call()
get_info(futures_info) %>% tail()
#> # A tibble: 6 x 3
#> ticker symbol value
#> <chr> <chr> <chr>
#> 1 SIA Comdty FUT_CONT_SIZE 5000
#> 2 SIA Comdty FUT_TRADING_UNITS troy oz.
#> 3 SIA Comdty FUT_VAL_PT 5000.00
#> 4 SIA Comdty FUT_TICK_SIZE 0.005
#> 5 SIA Comdty FUT_TICK_VAL 25
#> 6 SIA Comdty ID_BB_GLOBAL BBG00GH3MWD0
get_fields(futures_info) %>% head()
#> ticker instrument book symbol
#> 1: C A Comdty futures info SECURITY_NAME
#> 2: C A Comdty futures info FUTURES_CATEGORY
#> 3: C A Comdty futures info CRNCY
#> 4: C A Comdty futures info ID_MIC_PRIM_EXCH
#> 5: C A Comdty futures info FUT_NUM_GEN_AVAIL
#> 6: C A Comdty futures info FUT_CONT_SIZE
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.
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)
#> S4 object of class IndexMarket
#>
#> Slots inlude
#> tickers: access with get_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
get_tickers(index_market)
#> ticker
#> 1: SPXT Index
get_fields(index_market) %>% head()
#> ticker instrument book symbol
#> 1: SPXT Index index market PX_HIGH
#> 2: SPXT Index index market PX_LAST
#> 3: SPXT Index index market PX_LOW
#> 4: SPXT Index index market PX_OPEN
get_data(index_market)%>% tail()
#> ticker field date value
#> 1: SPXT Index PX_OPEN 2016-12-22 4323.10
#> 2: SPXT Index PX_OPEN 2016-12-23 4318.41
#> 3: SPXT Index PX_OPEN 2016-12-27 4329.87
#> 4: SPXT Index PX_OPEN 2016-12-28 4338.25
#> 5: SPXT Index PX_OPEN 2016-12-29 4298.86
#> 6: SPXT Index PX_OPEN 2016-12-30 4303.09
Retrieve the corresponding qualitative data for the abovementioned funds with:
index_info <- pull_index_info(source = "Bloomberg", tickers, verbose = F)
index_info
#> S4 object of class IndexInfo
#>
#> Slots inlude
#> info: access with get_info()
#> fields: access with get_fields()
#> call: access with get_call()
get_info(index_info) %>% tail()
#> # A tibble: 1 x 3
#> ticker symbol value
#> <chr> <chr> <chr>
#> 1 SPXT Index LONG_COMP_NAME S&P 500 Total Return Index
get_fields(index_info) %>% head()
#> ticker instrument book symbol
#> 1: SPXT Index index info LONG_COMP_NAME
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)
futures_TS
#> S4 object of class FuturesTS
#>
#> Slots inlude
#> active_contract_tickers: access with get_active_contract_tickers()
#> term_structure_tickers: access with get_term_structure_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
fund_market
#> S4 object of class FundMarket
#>
#> Slots inlude
#> tickers: access with get_tickers()
#> fields: access with get_fields()
#> data: access with get_data()
#> call: access with get_call()
#>
#> See also: get_periods()
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")
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.
Armstrong, Whit, Dirk Eddelbuettel, and John Laing. 2021. Rblpapi: R Interface to ’Bloomberg’. https://CRAN.R-project.org/package=Rblpapi.