University of Melbourne
Department of Finance
FNCE90045 Financial Spreadsheeting
Assignment 1 – Semester 2, 2023
The Stock Analyser Project
This assignment is worth 25% of the marks for the subject. The assignment is due at 11:59 pm on
Friday 15th September 2023. The assignment mark will take into account both the academic and
business quality of the work, and the quality of the presentation and layout of the material in the
Excel workbook.
Assignment 1 draws mainly from the material in sessions 1 to 6 of FNCE90045 Financial
Spreadsheeting.
Application software – the submitted Excel file must be compatible with the Windows version of
Microsoft Excel 365 (dynamic aware Excel). It must be based on an English (Australia) regional
setting for the Windows environment.
Workbooks - all your work must be submitted in one .xlsx file type workbook.
Read the requirements carefully before you begin the assignment. In work such as this it is
important that instructions are followed closely, but only begin when you are comfortable with the
structure and operation of the workbook, and when you understand the included Excel functions.
You are a member of an assignment group. The average group size is four members. This is a
group assignment, not four individual components submitted as a group.
You are strongly encouraged to work individually on all components of the project, discuss your
progress with all group members, then strive for consensus of the appropriate technique.
Although referring to concepts that you have already met, this assignment requires you to conduct
further research. However, you are required to follow material presented in FNCE90045 and/or the
https://excelatfinance.com/sessions/xlf90045.php material where possible.
FNCE 90045 Financial Spreadsheeting :: Page 2 of 8
Your response to each of the tasks posed must be accompanied by references to materials you have
used in your research.
The content of the assignment is examinable, and you should strive to understand the structure and
logic of the stock analyser project.
Assignment submission
Melbourne Business School (MBS) policy requires that all assignments be submitted through the
LMS assignment tab. A link is available on the FNCE90045 LMS site.
Assignments that are submitted by any other means will not be marked.
Late submission of an assignment is permitted only where an extension has been granted. Requests
for an extension must be made via the MBS Student Centre. Extensions can only be granted by the
MBS.
Requests for an assignment extension should be submitted here: http://go.unimelb.edu.au/yh9n
Before completing this form, please read the Assignment Extension Guidelines regarding acceptable
reasons for requesting an extension
Unless an extension has been granted, a late assignment will NOT be marked.
You are NOT permitted to resubmit a corrected version after the closing date.
Plagiarism and Collusion
Plagiarism (failure to cite your sources correctly and completely) and collusion (unauthorised
collaboration with another person to prepare an assessment task) are considered academic
misconduct and attract severe penalties. More information, including practical advice to students, is
available on the University’s Academic Honesty and Plagiarism website.
The Library and Academic Skills also provides excellent resources to help you reference correctly.
FNCE 90045 Financial Spreadsheeting :: Page 3 of 8
The stock analyser project
Contents:
Associated file: FSS-Ass1-y2023-vx.xlsx
The overall task is to take on the role of an analyst and complete the development of the Stock
Analyser provided in the FSS-Ass1-y2023-vx.xlsx file. This file is the starting point and you are
required to apply modifications and corrections and then complete the task according to
specification. The existing Stock Analyser workbook includes, in order:
1. Cover sheet
• Group details (group name, no student details)
• Project navigation (hyperlinks)
• References / About
2. Stock Analyser
• Selector/control panel (using data validation or form controls) + out of data (OOD) alert
(for new listings, and delisted stocks)
• Summary panel – statistics and co-movement
• Chart panel (primary stock) –dashboard charts (prescribed embedded charts)
• Comparison panel (secondary stock) – co-movement, and XY scatter chart with trend
• You will need to modify this worksheet – include XASX selector and other features
3. Summary sheet
• Column A – dates
• Columns B to F – primary stock (5 columns).
• Columns G to K – secondary stock / index
• Columns L to N – return formulas
• Column structure may require modification
4. Data worksheets
• XASX stock and index data
• Missing data x 1 (M Data) including
o Static range names for xxx_ stock
o You will need temporary name holders to suppress errors
o Note: this worksheet may not be needed?
5. WorkArea, including:
• Selector panel vectors
• The trading day date constructor for XASX, including the filter criteria
• Vector integrity and verification areas
Open the file and examine its structure carefully. You will see that the file contains the structure for
the project. This file will be the template for your assignment. Retain the ordering of the worksheets.
Save the file with a new name suitable for submission of the project when complete. The file is
referred to as the project workbook.
Basically it’s a data gathering and summarization exercise for a sample of stock market data.
Pricevolume data is gathered from a web source, checked for integrity, and summarised with a one
to one mapping of XASX trading dates. Data errors, if any, are resolved with a dynamic filter and
linked to a missing data sheet where missing observations are generated by last know value (LKV)
FNCE 90045 Financial Spreadsheeting :: Page 4 of 8
interpolation. The final reports are presented in dashboard style, with summary statistics, and
graphical presentation.
Other instructions are provided at the project groups meeting held in class 6.
The following major tasks are unordered and should be performed in a sequence decided by you.
Preliminary
Pay particular attention to the existing defined names in the associated file. Note that the BEN name
is a Table with WorkBook scope. Be careful not to accidently overwrite any provided formula. Check
the Formula Bar for any existing contents, then decide the action you need to take.
Here is a list of tasks with discussion points.
Cover worksheet
• Red header: Insert a business logo and your group name. The logo should be a stylised
version of your group member initials or similar
• From this point you are referred to as the Analyst(s)
• Navigation: Add a hyperlink based navigation system to all worksheets
• References: In proper academic style
• Features: a brief summary of your important contributions to the Stock Analyser worksheet
Y Data XASX worksheet
S&P/ASX 20
Materials sector:
• RIO Tinto Ltd
• Newcrest Mining Ltd
Consumer Staples sector:
• Coles Group
• Woolworths Group Ltd
References: https://www.asx20list.com/
ASX small caps
Materials sector (resources):
• Viagold Rare Earth
• Red Hill Iron
• Sayona Mining Ltd
• Lake Resources
References: https://stockhead.com.au/news/winners-list-here-are-the-top-100-asx-small-cap-stocks-
for-2021/
comparison indices
• S&P ASX200
FNCE 90045 Financial Spreadsheeting :: Page 5 of 8
• Appropriate ASX Sector Index(s) – as identified
Data “getting”
Identify the XASX codes for each of the stocks and indices.
Go to the Yahoo Finance web site: get all of the available historical daily price and volume data for
these companies and the indices data, for the last 5 years, plus period for part year to 31st August
2023. Do not use CSV data files.
Get: this is part of the Excel Data > Get & Transform > From Web procedure. Develop a set of query
strings to link the web data to areas of the YData worksheets. The query string has the form:
query1.finance.yahoo.com/v7/finance/download/STOCK_CODE?period1=START_DATE&perio
d2=END _DATE&interval=INTERVAL&events=history
when used in the From Web > Basic form
Choose Load To… in the query window
Then follow the Import Data dialog example.
Stack (vertically) the tables in order with each forming a separate contiguous region. Retain the
default formats, and identify each data set only by the default Table name.
In the WorkArea worksheet, include a list of XASX stock and INDEX codes. Name these WA.codes.
Write some formulas to count the number of data rows, and number of fields containing “null”
values for each of the data sets. Write the values (if any) as constants to preserve the information.
Name this WA.nulls
Data summary and transformation
A master list of trading days
In Column A of the Summary worksheet, insert a dynamic master list of trading days for the XASX, in
ascending order (A to Z, 1 to 9). Name (retain) the trading day list as Date (a dynamic name driven by
data size with workbook scope).
The master lists are initially created in the WorkArea worksheet. Insert a list of XASX non trading
days to create the mater list of trading days using a cascading function. The WA.TDList output is the
vector to use in Column A of the Summary worksheet. Use a dynamic link to the Summary
worksheet. Name this part of the WorkArea WS as WA.MasterLists.
Summary worksheet – data mapping and transformation
The Summary worksheet includes a master list of trading day – the analyst can overwrite the existing
seed data in this range. The Summary sheet has only three summary areas as described in point 3 on
page 3. The analyst should now be able to complete the B2:K2 formulas (– assigned to the GetData
name). The complex function applies to the Open,…,Volume range. Note that CodeAnchor is a
selector link, and the formula in the Returns column needs to be linked to the Return [change or log]
selector. The Cumulative return vector is used in a chart and accumulates returns (if required) in
date order for the period of DateV with base zero.
•
FNCE 90045 Financial Spreadsheeting :: Page 6 of 8
Summary worksheet – systematic error in Yahoo dates
Identification – after setting up the dynamic master list of trading days, load the available data for
one of the sample “large cap” companies. These are mostly heavily traded stocks so all data should
be available for the period of listing (or since listing).
Determine the start and end date for each #N/A series pattern. Take appropriate action.
Summary worksheet – no data, and missing data
No data – is the case where an initial listing or delisting occurs in the data window period (as set by
the selector panel). This case will activate the Out of Data alert at the bottom of the selector panel.
Missing data occurs – is the case where data is missing because of events such as trading halts, data
errors, thin trading, or Yahoo null errors. Use Find and Replace to change the Yahoo Unix Nulls to
N/As in the Y data records. This is the only permitted change to the source WEB data.
In this case (missing data) the missing observations are interpolated. These values must include the
formulas used in the LKV interpolation process. Apply a Yellow fill color to the interpolated cells on
the M Data worksheet
Missing data – identification
The GetData defined name returns a #N/A. Identification of missing observations is done on a stock
by stock basis. (After adjustment of any initial temporal errors and Yahoo Nulls)
1. Using the selector panel, select the first stock for the primary stock panel. This will load the
data in the Summary worksheet.
2. Identify any #N/A errors caused by missing observations, particularly in the ASX small caps
data. Other error types must be resolved, see page 3
3. Write an Advanced Filter criteria to filter all contiguous #N/A rows plus the preceding and
succeeding rows with valid data. Name one of the criteria as ASX_NA where ASX is the stock
code. You will need to resolve the Yahoo null errors
4. Copy the visible data for the filter, columns – dates to volume, and paste as values to the
range starting in column B of the M Data worksheet. Leave a blank row between each block
of company missing dates.
5. Repeat steps 1 to 4 for the remaining missing data in other stock codes.
Missing data – interpolation and identification
1. Use interpolation to replace the copied #N/A errors, and Yahoo transformed Nulls. Note:
find and replace is not the same as copy and paste
2. Using the example for BEN, and BEN_, enter an index vector ASX_, that will be used to form
a dynamic range for the missing data block, including any of the preceding and succeeding
rows.
3. The analyst is required to create a series of dynamic ranges based on the M Data column A
vector, ie. The ASX_ vector. The range names are applied to each of the interpolated blocks.
Note: the INDIRECT function is not compatible with a dynamic range based on OFFSET. Think
about the specification of the INDIRECT function!
4. Use helper cells on the WorkArea sheet. Name the area WA.MissingDynamic
Selector Panel
Q
•
•
•
•
FNCE 90045 Financial Spreadsheeting :: Page 7 of 8
Selector area 1 – Sector: allows a choice of {All stocks, Consumer staples, Consumer Discretionary}
from a List suitable for use in a Form Control (Group with Option Buttons?). This means replace the
Data Validation with worksheet controls. The sector determines the available codes in Stock Code 1,
and Stock Code 2. The circle 1 and 2 indicate that the Analyst must do action 0 first, then 1, then 2 as
the next step.
Selector area 2 Analysis date: allows choice of analysis date which is a sub array of the Date vector
on the Summary sheet. Change the data validation to a Form Control set of the form
Assume that selection order is Y > M > D, to allow only valid choices in the list. The list source for
each control is linked to the Date vector. In the WorkArea use functions such as UNIQUE, and SORT.
Name this are as WA.ADlists
Historic span: this is an array with Source named Span. Create the Span name in your Workarea
worksheet with window elements {5D, 10D, 1M, 2M, 3M, 6M, and 12 Months}. The historical span
ends on the end_date in the context of Excel date functions. This end date must be a trading day,
and is the “next trading” day (negative step) if the day falls on a non-trading day. Note: although
technically an error, the OFFSET function does allow negative height.
The analysis date and historic span drive the dynamic DateV vector. Other vectors such as OpenV,
HighV, … ReturnV … ReturnV2 (for the comparison stock) use DateV as the anchor reference.
Alternate names are DateA, OpenA, HighA if you prefer array terminology.
Return [change or log]: The analyst is required to transform the closing prices / index value vector to
a return vector or equivalent using two methods. This is included on the summary worksheet. Use
selector names {Delta Price, Log Price} with these formulas: i. Change in price/index where r_t= (pt –
pt_−1 )/ pt_−1 , and ii. Log return where r_t= ln(pt / pt_−1 )
Stock code 1: this depends on the setting in Sector, and is a selector with a list of all stock codes for
the sector.
Stock code 2 / index: is a selector list of all stock codes, for the sector, and the index series
Stocks are list alphabetically with the indexes last.
Summary panel (on the Analyser worksheet)
1. This section is straight forward and summarises data for the primary stock (Stock code 1). All
formulas should use reference ReturnsV or ReturnsA. There is no need to suppress any #N/A
errors here. Set the appropriate number of decimal places. No percentages
2. Start and End dates are integrity checks and are linked to DateV
3. The other elements are derived from panels 1 and 2 on the Summary worksheet
Charts
All charts should have dates in ascending order. The analyst should also eliminate weekend and
holiday gaps added by the Excel chart feature.
Include worksheet charts for:
1. Price volume charts (2 separate charts?).
2. Cumulative daily returns (have a think about this one!) Base zero for the period, in ascending
date order.
3. Return histogram compared to normal, with smooth line
•
FNCE 90045 Financial Spreadsheeting :: Page 8 of 8
And a ChartSheet for:
1. Open-High-Low-Close
In the comparison area:
1. An X Y scatter chart with trend
Avoid date clutter.
All charts are part of a dashboard and space is at a premium. To avoid clutter, omit chart elements
such as Legend, Chart Title, and Axis Title. Charts should be compatible with monochrome printing.
Use abbreviated formats for values such as M for volume and 2 decimal places for price. Apply to the
chart, and not the source data.
Notes
This assignment contains a long list of instructions. Further clarification messages will be issued as
needed!
The tasks are to be read in the context of materials covered in sessions 1 to 6 and the simulated
meeting from class 6
All returns should be based on Close price.
The Australian financial year ends on 30th June
The assignment is partly marked by an automated VBA process. Appearing to work, may not be
sufficient. It must stand up to the rigours of stress testing and comply with the principles of model
risk management.
Resources: https://excelatfinance.com/sessions/xlf90045.php#project1
General
Each group is required to conduct assignment discussion via the Group LMS Assignment 1 discussion
board. All group members must contribute to discussions before any queries are sent to the subject
coordinator. Marks are allocated to (may be deducted for?) this matter.
ENJOY the CHALLENGE