1/5
MTH792P - FINANCIAL DATA ANALYTICS
SAMPLE EXAMINATION PAPER
Semester B Final assessment 2020/21 (page 2 of 5)
2/5
Q11111111111eUESTION 1 Not yet answered Marked out of 25.00
(a) Assume you have a table named StaffSalaries with the following contents:
Display in table form the results of the following two SQL instructions (you might use the characters |, !, - or _ to
help display the table in the text response)
(i) [5 marks] SELECT Gender FROM LatestIncrease WHERE Bonus > Salary;
(ii) [5 marks]
SELECT Surname, Bonus
FROM StaffSalaries
WHERE Gender='Male' AND LatestIncrease>0.10;
(iii) [5 marks ] If the following instruction
INSERT INTO StaffSalaries
(Surname, FirstName)
VALUES('Anders', 'Sanders');
*does not cause an error*, what can you deduce about how the table was created?
(b) Say we have a table named AlbumCatalogue with fields: AlbumID, ArtistID, CurrentPrice,
ReleaseYear, and MediaFormat, and a table named Artists with fields: ArtistID, YearFirstWork, and
Country.
None of these fields is allowed to be missing in the database and the primary key for each of these tables is the first
field.
(i) [5 marks] Write the CREATE TABLE SQL instruction that creates the two tables. You are free to choose details
such as data type but make sure you make meaningful choices. Make sure that you codify the relationship between
the two tables so as to ensure referential integrity.
(ii) [5 marks] Write a SQL instruction that displays all the information (AlbumID, ArtistID, CurrentPrice,
ReleaseYear, and MediaFormat) for all albums with artists domiciled in USA (Country='USA').
(note that percentage quantities are equivalent to decimal numbers in the usual fashion, i.e. 10% represents the
number 0.10)
uestion 1
Semester B Final assessment 2020/21 (page 3 of 5)
3/5
Not yet answered Marked out of 25.00
(a) [5 marks]  Explain the result of the calculation mean(diff(v)) where v is an R vector. Given a simple example
using the digits in your student ID as the vector v.
(b) [5 marks] What does log-return mean? Explain step-by-step why the R instruction
diff(log(EURUSD$Close)) calculates the sequence of log-returns.
(c) [5 marks]  Find five incorrect lines in the following R code and provide fixes:
(assume all necessary libraries have been loaded)
(d) In calculating the volatility of an asset it is a typical mistake to use the actual asset values instead of the returns.
Assume that we have retrieved Apple share price data with:
AAPL <- get.hist.quote("AAPL")
(i) [5 marks] Write R code that calculates and displays the volatility of Apple close share prices.
(ii) [5 marks] Write code that calculates it incorrectly assuming you make the mistake described above.
Use an annualisation factor of 252.
Question 2
Semester B Final assessment 2020/21 (page 4 of 5)
4/5
Not yet answered Marked out of 25.00
(a) [5 marks] What does the instruction
import pandas as pd
achieve? How will your programme be different if instead you use
import pandas
(b) [5 marks] Spot and fix two errors in the following code:
(c) [5 marks] Provide Python code that will retrieve Amazon share prices (ticker ’AMZN’) for the last year and calculate
the average daily log-return over this period. Ensure you write code to load all the required libraries and use Adjusted
close prices.
(d) [5 marks] Write Python code that will read all corporate actions for Apple (AAPL) and calculate the total dividend
amount by adding all dividends (note that you will have to exclude all actions marked as 'SPLIT').
(e) [5 marks] Explain how R and Python libraries are different with regards to the notion of namespace.
Question 3
Semester B Final assessment 2020/21 (page 5 of 5)
5/5
Not yet answered Marked out of 25.00
(a) [5 marks] Explain what functionality is missing in an Excel pivot table that is provided by Power Pivot.
(b) [5 marks] Identify three different mistakes in the following VBA code, explain the nature of the error and provide a
fix.
(c) [5 marks] Explain the meaning of the Option Explicit instruction above and how it helps avoid mistakes.
(d) [5 marks] Say you have an Excel range with data about some trades as follows:
Write Excel functions that you could use in the following empty cells so as to automatically get the information about
the trade specified in the top right cell (for simplicity you can assume the columns above have 10 cells each). Assume
this top-right cell has been given the name ChosenTrade.
(e) [5 marks] Give two advantages of using an Excel table instead of a range.
Question 4