storethat together with pullit are the two workhorses for data ETL (extract, transform, load) work in the finRes suite. storethat provides an off-Bloomberg storage facility for financial data retrieved from Bloomberg using the pullit package. The package uses a SQLite database designed for easy and fast I/O (input/output) operations involving pullit data objects.
Install the development version with devtools::install_github("bautheac/storethat")
.
Start by creating a storethat database wherever suits (defaults to home folder):
Using the fields
dataset from the BBGsymbols package that gathers common Bloomberg data field symbols, the function creates a SQLite database bespoke to host financial data retrieved from Bloomberg using the pullit package. n
is a space/speed trade-off parameter; it refers to the number of tables created for hosting historical data for each financial instrument category. Historical data tables are indexed by unique combinations of ticker, field and date ids. Writing new data to a table involves reconstructing its index, a process that continuously increases in strain as the volume of data grows and the index gets bigger; spreading the data across multiple tables helps contain the strain expansion and eventually improves the writing time performance of the database at the expense of disc space. Should disc space be cheaper than time, which is often the case, select a high value for n
.
storethat provides store methods for all objects returned by the pullit package. Retrieve data from Bloomberg using pullit and store it in an existing storethat database using:
library(pullit)
storethat <- "~/data/storethat.sqlite"; end <- "2018-09-30"; start <- "2016-01-01"
tickers <- c("BP/ LN Equity", "WEIR LN Equity", "AAPL US Equity", "RNO FP Equity")
equity_market <- pull_equity_market(source = "Bloomberg", tickers, start, end, verbose = F, file = storethat)
db_store(equity_market, file = storethat)
#> ✔ Period 2016-01-04/2016-01-07 done.
#> ✔ Period 2016-01-08/2016-03-04 done.
#> ✔ Period 2016-03-07/2016-05-02 done.
#> ✔ Period 2016-05-03/2016-06-29 done.
#> ✔ Period 2016-06-30/2016-08-26 done.
#> ✔ Period 2016-08-29/2016-10-21 done.
#> ✔ Period 2016-10-24/2016-12-20 done.
#> ✔ Period 2016-12-21/2017-02-16 done.
#> ✔ Period 2017-02-17/2017-04-13 done.
#> ✔ Period 2017-04-17/2017-06-12 done.
#> ✔ Period 2017-06-13/2017-08-09 done.
#> ✔ Period 2017-08-10/2017-10-06 done.
#> ✔ Period 2017-10-09/2017-12-01 done.
#> ✔ Period 2017-12-04/2018-01-30 done.
#> ✔ Period 2018-01-31/2018-03-29 done.
#> ✔ Period 2018-04-02/2018-05-25 done.
#> ✔ Period 2018-05-28/2018-07-23 done.
#> ✔ Period 2018-07-24/2018-09-19 done.
#> ✔ Period 2018-09-20/2018-09-28 done.
Check the content of an existing storethat database at anytime with:
snapshot <- db_snapshot(file = storethat, instrument = "equity", book = "market")
head(snapshot)
#> instrument book field ticker start end
#> 1: equity market CUR_MKT_CAP AAPL US Equity 2010-01-04 2018-09-28
#> 2: equity market CUR_MKT_CAP BP/ LN Equity 2000-01-04 2018-09-28
#> 3: equity market CUR_MKT_CAP RNO FP Equity 2010-01-04 2018-09-28
#> 4: equity market CUR_MKT_CAP WEIR LN Equity 2000-01-04 2018-09-28
#> 5: equity market EQY_SH_OUT AAPL US Equity 2010-01-04 2018-09-28
#> 6: equity market EQY_SH_OUT BP/ LN Equity 2000-01-04 2018-09-28
Update content in an existing storethat database using pullit:
storethat_update(
instrument = "equity", book = "market", name = "ADM US Equity", file = storethat, verbose = F
)
Higher order updates are also allowed:
storethat_update(instrument = "equity", file = storethat, verbose = F)