Financial Modelling - Final Assessment
Market Bubble Burst Test
Background
The first financial market bubble can be well traced back to the 17th the Netherlands,
during which demand for the cheerful tulip bulbs caused farmers to experiment with species
and colouring so that the tulip became an object of speculation. In fact, they were so prized,
people literally mortgaged their houses during that period in order to buy and then resell
tulip bulbs. Suddenly, consumer confidence eroded, resulting in the tulips market crash.
They became all but worthless. It is believed that the bubble burst led to a year’s-long
economic decline.
Exhibit 1. Market Bubble Formation and Burst
As shown in Exhibit 1, when a market bubble bursts, demand falls, and prices decline
quickly. Investors who established positions near the top could see their profits erode
completely. Depending on its size, a deflating bubble can have short-term effects to an
industry or market niche, but it could also prompt large scale consequences [1], such as the
recent Global Financial Crisis, triggered by the deflation of the US housing market during
2007–2008. The downturn in the U.S. housing market snowballed into a national recession
and led to a global monetary crisis. Therefore, a close examination of market bubbles and
the assessment of economic and financial after effects of the bubbles bursting has become
an integral part of the asset management.
Market Bubble
In this exercise, a bubble is characterized by an increase in the price of assets at a rate which
is not sustainable, shortly after, if a bubble has formed the price will decrease at a high rate,
leading to a large potential loss if the asset is not sold quickly enough.
Momentum
The momentum of an asset relates to the acceleration of its price. It can be determined by
simply calculating the rate of change of the asset’s price over a certain period of time.
Random Search
A brute force numerical method used to search for local minima that increases efficiency by
checking every ‘nth’ value, with the assumption that the elements located close to every
‘nth’ value will not deviate too far from it. A smaller sample is then extracted from the data
set and these values are checked individually to find the minimum value.
The company you are working for currently uses an in-house VBA function to identify
bubbles in the market by analysing the asset’s momentum. Currently, local minima of the
asset’s momentum are found utilizing a random search to boost efficiency, the momentum
of the asset around these local minima are then analysed to check if they are too low, which
could indicate a bubble has burst. You are given this function by your supervisor and she
wishes you to demonstrate that you understand how the code works and make the function
more generic (Sheet 1) and would like you to improve upon it (Sheet 2), if applicable.
Sheet 1
You must alter the function Bubble_Finder (Module 1) so that it takes inputs
Bubble_Finder(StockPrices, Dates, BigStep, NoBubbles) in that order. You must then
analyze the assets given and place the results, starting in the top left corner in the green
cells provided next to each asset’s name. Exhibit 2 is an example given in the sheet of what
the output should look like for an analysis of 2 potential bubbles for Z1P.
Exhibit 2. Market Bubble Formation and Burst
Z1P Average Momentum Momentum Std Dev Local Min Momentum Local Min Location Bubble Burst Found
0.017255565 0.103020374 -0.369458128 16/03/2020 Large Bubble Burst
-0.1 03/06/2019 No Bubble/No Burst
For each other three assets, you must use the parameters below:
1. CBA
Step size of 5 weeks
Analyse 3 potential bubbles
2. QAN
Step size of 3 weeks
Analyse 2 potential bubbles
3. AIZ
Step size of 4 weeks
Analyse 2 potential bubbles
Additionally, the date of the local minima must display the date, not the numeric code for
the date, as shown in the 5
th column of Exhibit 2.
Please only make the required changes to the code which relates to the above mentioned
inputs and output. Do not alter the method used to search for minima or the decision rule
which is used to classify if a bubble has burst in this sheet.
Sheet 2
This is a copy of Sheet 1 and will not be marked as part of your spreadsheet submission,
instead it is to be used to present what your changes to the code supplied do. You are free
to make any changes you would like at all to the code in the Bubble_Finder_Exp() function
(Module 2) that you believe will improve the code supplied.
The Assessment
The sample code to create the required function for this task is already provided to you in
Module1, but it was very poorly written and unable to proceed effectively straightaway.
Your job is to fix and improve it (if applicable). You will be assessed across Week 11 and
Week 12.
1. Week 11 Assessment Q&A (5 Minutes)
The focus of the assessment in this week is on the understanding of the idea (like a code
plan), including but not limited to:
i. Discuss and demonstrate a code plan for the functions;
ii. Identify and discuss some of the bugs and issues with the code;
iii. Suggest methods/ideas to improve the efficiency of the codes;
iv. Recommend on how to optimise the functions;
2. Week 12 Assessment, Presentation (5 Minutes Max)
The focus of the assessment in this week is on the code and solution quality, including but
not limited to:
i. Efficiency;
ii. User interface (user friendly wise);
iii. Accuracy of bubble detection;
iv. Bubble detection rules;
v. And general usability and improvements you have made/proposed.
3. Spreadsheet Submission in Week 11 and 12
As previously, the automatic marking software marks your work.
i. It will only mark Sheet 1 worksheet and the function;
ii. You must not modify the structure of Sheet 1, change the function name or the
order of its input parameters.
I couldn’t wait to meet you in Week 10 and Week 11 assessment. Please ‘think outside the
box’ and let your wild imagination lead you while tackling this challenge.
Good luck!
Bob
Reference
[1]. Pavlidis, E., Yusupova, A., Paya, I. et al. Episodes of Exuberance in Housing Markets: In
Search of the Smoking Gun. J Real Estate Finan Econ 53, 419–449 (2016).