1) Invoke FinMath.xls
Allow permissions for enabling the macros
2) Select the Reference sheet (first one) slide1_0001 or slide2_0033
[after reading, click "ok" to any message boxes that may appear] slide3_0024
Populate the set of stock asset time series for this session,
either from the list of previously saved series via the Open Dataset
button form slide5_0034 slide6_0035
or from the Yahoo financial WEB site over the Internet via the Get Equity
Data button form. slide4_0030
A transient sheet, labeled with the name of the stock asset time series
is created, but not imposed as the top active spreadsheet. slide7_0032
Repeat the procedure until the full set is populated. slide8_0004
When using Get Equity Data, it is useful to explicitly set the Start and End
dates to a common set for all the stocks. While not essential,
it facilitates recognizing which series were download in the same session
if you want to reuse the series in a later session via Open Dataset.
Any successfully downloaded series will be also be saved to the same
..\Data\TMP folder as the previously saved series.
To keep the calculation times in the latter steps fairly quick and stay
well safe within the numerical precision limits of the matrix inversion,
use a Start to End range of 6 years or less, and
use no more than 8 stock time series as a set.
If you want to perform an analysis using an market index as a member of
the set, do not replicate the members of the index in the set;
use a market proxy, e.g. OIH for oil producers, SPY for SP500, etc.,
as a member.
3) Select the Asset Browser sheet (second one)
[reminder, the series are numbered starting with "0"]
Using the Series # Up/Down button forms, select the first, "0" series
and perform Reset+Plot. slide9_0005
Note, the start and end dates of the selected series.
While at least one Reset+Plot is necessary to establish internal variables,
it is recommended that one cycles through selecting and
Reset+Plot'ing each of the time series. slide10_0006 slide11_0007
Viewing the plots will help provide insights,
if the portfolio exhibits unexpected yield behaviors later on.
Note, selecting a new Series and invoking Plot without a immediately
preceding Reset will result in the reuse of the prior start and end dates
for the attribute calculations and charts. slide12_0008
Compare with new Series Reset+Plot. slide13_0025
4) Select the CoVariance Analysis sheet (third one)
The CoVariance Analysis sheet encapsulates the CAPM formulation.
[note, the residual calculated data from the prior session]
Upon first viewing, the sheet reflects the old values from the prior
saved session. slide14_0010
Click the Reset button form,
the Start and End dates should reflect the smallest common date range
across all the asset time series.
Click the Calculate button form, to perform a first set of calculations.
The series information and the returns at each trading period point
(since we are keeping the trading period as 5 days, the period points are
every 5+2weekenddays= 7 days) will populated.
However, the weights and matrices still reflect the effects
of the residual calculations from the prior session. slide15_0011
Reset+Calculate, again, to clean things up. slide16_0012
When the covariance matrix is determined directly in the Covariance Analysis
sheet by Reset+Calculate, the values of the matrix, the portfolio member
weights, etc. reflect using ALL the samples between the Start and End dates,
not the Training Interval.
5) Select the Dynamic Optimal Portfolios sheet (fourth one) slide17_0013
[note, parameter settings for all combinations of "weights", "trading periods"
"training period length", and "risk-free return" are entered on this sheet.]
The Dynamic Optimal Portfolio sheet reuses the Covariance Analysis sheet to
perform part of its calculations and in the process overwrites many of the
entries in the Covariance Analysis sheet.
When the Covariance Analysis sheet calculations are invoked
from the Dynamic Optimal Portfolio sheet,
the values of the matrix, the profolio member weights, etc.
reflect using a "rolling" window of the samples within the Training Interval
set in the Dynamic Optimal Portfolio sheet,
except for the last window which may use 1 less sample.
Set the parameter combination of "weights", "training period length", and
"risk-free return for this cycle.
Changing the Training Interval shifts the first trading date.
To make valid comparisons for analyzing the effects of different Training
Intervals, it is necessary to "play" with the Start Date to force the
same first trading date.
Click the Reset button form, the populated Start and End dates should match
the smallest common date range across all the time series
in the CoVariance Analysis sheet Reset. If they do not, Save As this
session to a errorFinMath.xls file, we want to the information to track
down a spurious problem.
Click the Calculate button form. Wait, it may take over a minute.
The Optimal Portfolio Chart sheet will "pop-up"; slide18_0014
record the information for this cycle.
Note, the CoVariance Analysis sheet now reflects the calculated CAPM
variable values for the last training period window. slide19_0015
6) Exit or Exit and Save the FinMath.xls session
Saving the session will retained the last set of calculated values, charts,
and parameter values & settings for the next session,
however the asset time series and transient data sheets are Not retained.
7) FinMath.xls can be used for experiments such as comparing the effects
of reducing the maximum asset weight constraint from 200% to 100%.
slide21_0016 slide22_0017 slide23_0003 slide24_0018
Note, the change in "jitter" in trading values and chart
and compare the final value, volatility, and Sharpe Quotation.
8) Effects of changing the Training Period Window Length from 10 (eqv to 50 business
trading days) to 7 (eqv to 35 business trading days).
(For a valid comparison, the first trading date for both cases should have been
adjusted to align, but was not before the graphics were processed.)
slide26_0003 slide27_0018 slide28_0019 slide29_0020
[note, FinMath.xls is a workbook of "live" spreadsheets and
out of necessity (and lack of time), the cells can be selected and changed
accidently. Protecting a cell is insufficient and easily deleted or replaced.
Hopefully, if an accidental change occurs, the spreadsheet will "hang"
or the PC will "hang.
Use the Task Manager (cntl+alt+delete) to end the Excel task or
if all else fails, perform a power reset.
Remember, any successfully download time series is retained and found
via Reference > Open Dataset ..\Data\TMP folder, so all is not lost.]