联系我们： 手动添加方式: 微信>添加朋友>企业微信联系人>13262280223 或者 QQ: 1483266981

Objectives
The objective of Coursework Two is to demonstrate a practical understanding of building data pipelines in order to extract data from a database, manipulate data and then load into a database.

In order to achieve this objective, the student can use R/Python programming languages and perform the following tasks:

extract from a database using Python/R, transform data with Python/R and load transformed data into a database.
extract data from a database, load into another database and transform data using a set of stored database procedures.
The student should select one of the following use cases:

Use case: incorrect trade detection
Traders submit single trades at any point in time during the day of 2021-11-11 & 2021-11-12. Traders might make mistakes when they submit their trades, it is your responsibility to verify that trades submitted by Front Office are genuine and in line with market expectations. If a trade is deemed to be suspect, (i.e. incorrect trade price or incorrect quantity), it must be reported.

For each day, the objective of this use case is to:

retrieve all trades as per end of day (i.e. one run for all trades on 2021-11-11 and one run for 2021-11-12) from MongoDB db Equity collection CourseworkTwo (see instructions below);
for each day, check that trades are consistent with expectations (i.e. there is no fat fingers error, inconsistency between quantity traded and notional amounts, genuine mis-pricing on the trade); Here, you can apply any model to detect whether a trade is genuine or not. This can be done in multiple ways (trade vs other trades, trade vs price, hypothesis testing, clustering analysis), any approach is valid however please describe in the methodology section why you pick one approach over another.
if any, create a new table in SQLite Database called trades_suspects. Load suspect trades into a new table in SQL. The creation statement for this table should be stored in “./modules/db/SQL/CreateTable.*” where * should be replaced by .py or .R. Table should be designed to follow best practices and must then include a primary key and a foreign key.
aggregate Quantity and Notional for all trades by date, trader, symbol, ccy and insert the results into portfolio_positions.(see section on Database description)
Use case: risk policy breach
Second Line Risk has agreed with us (Front Office) that we will be monitoring all risk positions for our traders. In particular, for 2021-11-11 & 2021-11-12 end of day, we will need to:

retrieve all trades as per end of day from MongoDB db Equity collection CourseworkTwo (see instructions below);
aggregate all trades by Trader, ISIN, Currency and Date;
retrieve all policy limits from SQLite database;
check if any new aggregated position is breaching a policy limit (check for at least one of the below policy limits);
if a policy limit is breached, design and create a new SQLite table called policy_breaches and load all breaches into the new table. The creation statement for this table should be stored in “./modules/db/SQL/CreateTable.*” where * should be replaced by .py or .R. Table should be designed to follow best practices and must then include a primary key and a foreign key.
load all trades into portfolio_positions (see section on Database description)
Policy limits description:

if limit_end is not null, limit has been changed or decommissioned;
long/short consideration: max amount in USD$ (mark to market) for a single stock position;
volume relative (%): max position given daily volumes. The trader cannot have more than X% of daily volume as ratio of position consideration. position must be mark to market over the volume;
sector relative: max concentration of portfolio allocations by sector. all stocks in a portfolio for a sector cannot exceed the X% specified. this is the result of sum portfolio value as quantity times market price (mark to market positions) by sector over the total mark to market portfolio value.
ES relative (%): the trader cannot run a portfolio that has an expected shortfall greater than X%. Expected shortfall is calculated as the average of 5 biggest losses of the last 50Days portfolio returns with an holding period of 3 days (i.e. Price today / Price 3 Days ago – 1)
Volatility (%): the trader cannot run a portfolio that has a portfolio annualised return volatility greater than the limit express in percentages. Look-back for volatility calculations is 50Days.
VaR Relative (%): the trader cannot run a portfolio that has a Value at Risk greater than X%. Value at Risk is calculated on the fully revaluated portfolios returns, look-back period 50Days, holding period 3 days, 99% confidence level. Value at Risk can be estimated by using either historical simulations or the Variance-Covariance Method. If the Variance-Covariance Method is used, all parameters described above should be used to estimate the portfolio variance over 50Days. The assumption of normal distributions of returns is accepted.
Use case: benchma