Closed

# R & VBA programmer - repost

This project was awarded to rouxelite for \$150 USD.

\$30 - \$250 USD
9
###### Project Description

1. Use R to complete) Create a transition matrix showing the average stock turnover of each quintile.

For example, if there are Nt stocks in Quintile 1 in time T, and 50%/20%/10%/10%/5% of them migrated to quintile 1/2/3/4/5 respectively in time T+1, the turnover rate vector (1X5) for Quintile 1 in T+1 will be 50%/20%/10%/10%/5%. The transition matrix will show the average turnover rates over time for each quintile.

All necessary data are ready on the tab namely “Q2 data”.

You can assume the following,

1. Turnovers are calculated since Jan 2006.
2. Stocks associated with “NA” at time T+1 are excluded from calculating turnover at T+1.
3. Any stocks moving outside the universe at time T+1 are excluded from calculating turnover at T+1.

The R program must include the below setting,

1. Time range: Start month and end month, ie flexible for selecting a period between Jan 2006 and July 2013.
2. Periods: “Monthly”, “Quarterly”, or “Semi-annually”, ie turnover over next 1, 3 or 6 months respectively.
3. Sector: sector grouping can be “All”, “Financials”, or “Non-Financials”, ie turnover within the whole universe/all stocks, Financials sector or Non-Financials sector.

2 . (Use VBA to complete) Create a Wiener process constant drift rate model and portfolio return simulator.
__
dx = a dt + b dz where dz = ε √dt ε ~ N(0,1)

“a” is the expected drift rate.
“b” is the standard deviation of the Wiener process.
“ε” is the random variable under normal (0,1) distribution.

Apply an investment strategy on an index which follows the Wiener process. The simulator can generate the expected return of the strategy and create a distribution graph of the portfolio yearly returns base on the simulations (see sample graph below). Data and output must be in separate worksheets.

The investment strategy is depending on the index level compare with the pre-defined upper and lower limit. The strategies are as follow:
1) If index level > upper limit, hold 20% index and 80% cash.
2) If lower limit < index level < upper limit, hold 50% index and 50% cash.
3) If index level < lower limit, hold 80% index and 20% cash.

For example, if the upper limit is 12000 and lower limit is 8000, the strategy will be:
Date End of date
index level Beginning of the date portfolio weight
Index Cash
Initial 10000 50% 50%
Day 1 11000 50% 50%
Day 2 13000 50% 50%
Day 3 14000 20% 80%
Day 4 13000 20% 80%
Day 5 10000 20% 80%
Day 6 7000 50% 50%
Day 7 7000 80% 20%
Day 8 6000 80% 20%
Day 9 9000 80% 20%
Day 10 10000 50% 50%

You can assume the following:
1) Cash return is zero.
2) Each simulation is 1 year long and 1 year has 250 trading days.
3) Index level and portfolio NAV are start from 10000.
4) Portfolio rebalances at the beginning of the each day.

The simulator must be flexible enough to handle:
1) Difference “a”, “b”, upper limit and lower limit given by the investor.
2) As many simulations as the investor needs.

## Hire Freelancers who also bid on this project

• Forbes
• The New York Times
• Time
• Wall Street Journal
• Times Online