satrapade / pairs

1 stars 1 forks source link

pair_reporting

data, workflows for pair performance reporting

Scripts and results are in th N:\Depts\Share\UK Alpha Team\Analytics directory.


A task is setup on the Windows Task Manager to run every day at 6:30 in the morning:

image

The task is setup to run Rscript.exe:

"C:\Program Files\R\R-3.4.3\bin\Rscript.exe"

with this parameter:

-e source('N:/Depts/Share/UK\x20Alpha\x20Team/Analytics/Rscripts/workflow.R')

workflow.R then executes individual workflow steps.


The N:\Depts\Share\UK Alpha Team\Analytics\Rscripts directory contains all workflow scripts:

File Description Documentation
workflow.R the start script here
workflow.log results log here
relevant_equity_ticker_table.sql construct date, ticker, bucket tables here
initialize_scrape_db.R make sure the sheet scrape DB exists here
perform_sheet_scrape_to_db.R scrape latest sheet here
create_cix_uploads.R | create BLOOMBERG CIX formuli here
create_database_temp_tables.R | call SQL scripts here
create_market_data.R | fetch relevant market data from BLOOMBERG here
create_market_data_intraday.R | fetch relevant intraday data from BLOOMBERG here
create_pair_icons.R create icons for live pairs here
create_portfolio_summary.R create portfolio summery from sheet scrapes here
create_portfolio_upload.R create portfolio upload files here
create_risk_reports.R run .Rnw files to create risk reports here
create_tsne_grid.R compute 2D layout of pairs, live stocks here
intraday_bank_pairs.R fetch intraday price action for SX7P components here
intraday_fx.R fetch intraday price action for 30 currencies here

The N:\Depts\Share\UK Alpha Team\Analytics\risk_reports directory contains all knitr Rnw files used to generate reports:

File Description Documentation Example
trailing_stop_report.Rnw trailing stop-loss report here here
what_happened_last_week.Rnw what happened last week report here here
custom_ABC_report.Rnw analysis of ABC's pairs here here
custom_AC_report.Rnw analysis of AC's pairs here here
custom_DH_report.Rnw analysis of DH's pairs here here
custom_GJ_report.Rnw analysis of GJ's pairs here here
custom_JR_report.Rnw analysis of JR's pairs here here
custom_MC_report.Rnw analysis of MC's pairs here here
pair_risk_contribution.Rnw pair risk contribution here here
risk_plots.Rnw risk plot here here
portfolio_summary.Rnw portfolio summary statistics here here
bank_pair_report.Rnw search for trending bank pairs here here
tech_pair_report.Rnw search for trending technology pairs here here
fx_trend_report.Rnw search for trending FX pairs here here
market_data_status.Rnw market data fetch here here
scrape_status.Rnw sheet scrape status here here
sheet_scrape_report.Rnw sheet scrape report here here

The production database contains product and bucket history which is used to create the above reports. some amount of pre-processing is done on the SQL server, which results in the following intermediate tables. The SQL scripts used to create these tables are in the sql folder.

Table Description Script
ttBUCKET_EXPOSURES exposures by bucket create_ttBUCKET_EXPOSURES.sql
ttBUCKET_PNL pnl by bucket create_ttBUCKET_PNL.sql
ttBUCKETS relevant buckets create_ttBUCKETS.sql
ttBUID_MARKET_STATUS market status of relevant BUIDs create_database_temp_tables.R
ttCURRENT_ISIN_MARKET_STATUS market status of current ISINs create_database_temp_tables.R
ttDATES relevant dates (including weekends) create_ttDATES.sql
ttEXPOSURE_SECURITIES relevant instrument table create_ttEXPOSURE_SECURITIES.sql
ttHISTORICAL_BUCKET_EXPOSURES exposures over time create_ttHISTORICAL_BUCKET_EXPOSURES.sql
ttHISTORICAL_BUCKET_HOLDINGS holdings create_ttHISTORICAL_BUCKET_HOLDINGS.sql
ttHISTORICAL_BUCKETS all buckets create_ttHISTORICAL_BUCKETS.sql
ttHISTORICAL_EQUITY_ISIN map historical to current ISINs create_database_temp_tables.R
ttISIN_MARKET_STATUS old ISIN status create_database_temp_tables.R
ttTICKER_MARKET_STATUS ticker status create_database_temp_tables.R