代写FNCE90045语言、代做Java语言编程、代写Python,C/C++程序

日期：2020-10-26 10:46

FNCE90045 Financial Spreadsheeting, Assignment 2

Complete each of the following tasks:

Part A :: function procedures – module Part_A (8 marks)

1.. An analyst has developed code to emulate the WS DAYS360 function

The VBA code has syntax:

Source: https://excelatfinance.com/xlf20/xlf-emulate-days360-nasd.php

Required:

In your Part_A module, create a new function named xlfDAYS360NASD_v2 that also

allows for the case where start_date > end_date. Also consider whether the return

value equivalent to -DAYS360 (negative DAYS360)? If so, is it always the case?

[You might need to think about this issue]

2.. Required: Create another UDF to emulate the WS function DAYS360 European

method. Name this procedure xlfDAYS360EURO

3.. Required: Create TestMacro procedures for parts 1 and 2. Use relevant dates from

the FNCE90045 materials to demonstrate code operation.

FNCE90045 Financial Spreadsheeting, Assignment 2

Semester 2, 2020.

5

Part B Macro procedures (13 marks)

Required: Insert a Code Module and name it Part_B.

Write a Macro procedure named Pricer, to emulate the WS PRICE function.

The WS function has syntax:

PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

See: WS PRICE function for details

Only basis 0 and 4 are required in this section

An important feature is inclusion of an incomplete period between settlement and first

coupon. Described as DSC (days to first coupon) in resource #2,

Use your custom function from Part A to handle the time component. These can be called

from this module. More information about the count basis can be found at 1998-ISDAmemo-EMU-and-Market-Conventions-Recent-Developments.pdf

and Coupon Interest and

Yield for eTBs

Be aware that the price formula includes summation operators, so apply your knowledge

of For…Next loops to this part of the procedure.

The main macro, named Main will display an InputBox with comma delimited

parameters. Clicking OK will pass the parameters to the Pricer macro, this means that

Pricer has arguments. On completion, a MessageBox will display the result details. The

MessageBox activation technique is your choice.

Demonstrate relevant combinations of the input parameters including the values from

example 1 in resource #2. These can be the default parameters to the InputBox

Unsure that the code is readable!

Resources

1. Bond pricing - nominal-treasury-bond-calculation-coupon.pdf

2. WS implementation of example 1 from reference 2 xlf-bond-dsc-ass2s1y20.xlsx

[14 KB]

FNCE90045 Financial Spreadsheeting, Assignment 2

Semester 2, 2020.

6

Part C User interface + (10 marks)

The Correlation, and Covariance, Analysis Tools available in the Analysis ToolPak Add-in,

suffer from a number of operational deficiencies.

Your task is the develop a UserForm based tool, that solves these operational deficiencies, and

combines both Correlation and Covariance into one interface.

The test data set for the part, is four companies from the assignment 1. Your Part C WS will

have a Date column, followed by four columns of returns data with header row, as a

contiguous range. Time period is FY20.

Required:

1.. Add a UserForm module named Part_C, then create the following UserForm

interface.

Include all controls, and color scheme as shown.

2.. Colors include

RGB(0, 0, 255)

RGB(255, 204, 102)

RGB(255, 255, 153)

RGB(255, 255, 204)

3.. Event :: Initial display of interface

Input Range (default)

The Address of the CurrentRegion (ReSized to exclude the Date

column)

FNCE90045 Financial Spreadsheeting, Assignment 2

Semester 2, 2020.

7

Subject to, the CurrentRegion – Column A being valid dates, and other

Return columns being valid values (such as Numeric and no Errors)

Labels in First Row (User selected)

4.. Comovement type

User selected (includes combined choice). Covariance is the default setting.

5.. Output Options

User Choice

6.. Click for Help

Displays User Help feature. (Message Box or UserForm based)

7.. Submit your completed work with full output sent to the top region of the Part C

WS.

Resources:

Part D The open project (15 marks)

In this section you have the opportunity to develop a VBA based solution to a challenging

BUSINESS task in the Excel environment.

Whilst the project is open and can be based on any discipline from the Faculty of Business

and Economics, your idea must be in the spirit of Financial Spreadsheeting and does require

development of a Project Proposal (Specification).

Proposal ideas may include a calculation engine, input interface, and output interface. You are

encouraged to include data analysis and graphical (chart) output.

FNCE90045 Financial Spreadsheeting, Assignment 2

Semester 2, 2020.

8

The proposal: Describe the project’s function, with details of the finance, Excel, and VBA

components. The financial spreadsheeting proposal, including a title, a list of VBA

components and associated finance features, should not exceed the equivalent of 1 page of A4

paper.

The proposal is for use by the group members and does not require prior approval.

The proposal is your main avenue available to “air your ideas” as to the projects content and

validity to all group members. You are required to submit a copy of the original proposal,

which now forms the plan for the project, and the completed project, within the assignment

workbook. By this stage it should also include an outline of the plan to completion. Include it

as text in a scrolling UserForm window.

Be mindful of the project’s presentation. When opened, will the project’s purpose, features,

and operation be obvious.

General (4 marks)

Your workbook must include, but may not be limited to, the following sheets, in order:

Name: Introduction, including a range named WorkArea.

Name: Part A (even if not used)

Name: Part B (even if not used)

Name: Part C (even if not used)

Name: Part D (even if not used)

The Introduction should include:

? Your group name and logo (image)

? A list of all group members – student numbers only

? A series of navigation buttons – that provides access to your answers on the WS,

including, if needed, the WorkArea

? A list of references in proper academic style

Assignment question protocol

Any assignment question must be discussed by group members in the first instance. If

necessary, the group leader is then able to discuss the matter at the scheduled ZOOM

consultation sessions in weeks 10 to 12. Reminder, this work is part of your

assessment.