UDF留学生代写、Python/CS/C/C++设计代写、代写代做Java/Matlab编程

日期：2018-10-05 09:57

Complete each of the following tasks:

All code modules must include Option Explicit in the Declaration section.

Do not use class module.Ensure that all code works and does not return a Debug error. Include

comments where appropriate.

Never use Add-ins and XML in Excel Developer, use Code and Controls.

Only use VBE in Excel, not in other Microsoft.

Write a word profile to illustrate all your work procedure in the Excel (the steps, the coding

and comments, the result show in the excel and so on).

Submit the word and the excel profile at the same time. Remember all your excel work should be in one workbook.

Part A: Function procedures

Insert a Code Module and name it PartA.

i. Write a Public (UDF) Function procedure to estimate the amount of Land Transfer

Duty payable by the purchaser or acquirer of a property (including a house) in the

state Victoria. Information on the rates can be found at

http://www.sro.vic.gov.au/land-transfer-duty and

http://www.sro.vic.gov.au/node/1491.

The function is to be used for current transactions, and also audit of earlier

transactions. The audit period commences on 21 April 1998. Name the function

LTDuty with arguments Land_value (in whole dollars) as a type long, and Dte (a

serial date value, ie. an Excel date) with suitable type.

ii. Write a Public (UDF) Function procedure named YDate with syntax:

YDate(asx_date,[switch_dates])

The YDate function returns the YAHOO date, adjusted for seasonal time

adjustments, corresponding to the ASX trading day date.

The YDate function has the following arguments:

asx_date Required. A date that represents an ASX trading date

switch_dates Optional. An optional list of two or more dates to align the ASX

trading dates with the YAHOO data dates. The list can be either a range of

cells that contain the dates or an array constant of the serial numbers that

represent the dates.

iii. Write 2 (two) private function procedures suitable for use in a VBA stock option

analytics package. The two functions can return values for any two of the

following option measures.

a. Delta

b. Gamma

c. Theta

d. Vega

iv. Include a set of simple test procedures for parts i to iii

2

Part B: Macro automation – filtering and interpolation

Insert a Code Module and name it PartB.

i. Setup worksheets – NO VBA code required in this section

A company data worksheet is provided, which is named as EUR.AX. Use data

for a 1calendar year period.

The Summary worksheet is provided, but may need your modification.

Setup the trading day vector of the Summary worksheet to accommodate the

data from the data worksheet.

Add an M Data worksheet.

The GetData formula should be converted to a UDF routine that incorporates

the YDate routine from PartA. Include a duplicate copy of YDate in this

module to accomplish this task. In this section your plan B should be, modify

the GetData formula to include YDate.

ii. Write a Macro procedure to copy the #N/A records from the Summary sheet to the M

Data sheet, in a form, suitable for interpolation

iii. Write a Macro (or group of macros) to interpolate the data by adding cell formulas,

applying a color scheme, and naming the interpolated data region. At this point the

#N/A errors should be eliminated from the Summary data.

iv. Write a Macro to reset the Missing Data set. In other words, undo the effects of points

ii and iii. Cell formats must also be cleared. This macro is useful in the code

development phase.

v. Add a set on worksheet buttons, on the Summary worksheet to demonstrate the code in

this section (part B)

vi. Submit your work with the macros executed, that is, the M Data worksheet populated

3

Part C: On Sheet interface

Your task is to convert the Option Pricer user-form based routine to an “on-sheet controls”

version. The Option Pricer routine (Option Pricing Program), image and code, is available in a

number of workbooks including Sample1.xlsm and Sample2.xlsm.

The on-sheet version is to include the following revisions and features:

1. Worksheet name: Option Analytics

2. Worksheet code name: Sheet3

3. The FrmOptionPricing engine and interface functionality

a. The existing Option Parameters validity test: If Not IsNumeric(temp)

And temp <> "" And temp <> "-" Then is to updated to a

KeyPress event version. This can be applied to each element, or as a separate

procedure

b. Include a frame for your stock option analytics procedures developed in part A.

This code can be copied to the Sheet3 module.

c. The existing Output Results frame is to be replaced by Output to a Message

Box, and / or Output to Result Table, a section of the Option Analytics

worksheet. The Result Table should be in the spirit of the VBE immediate

window. For each output event, a record is created with a date time stamp in

the first field. Include a button to clear the output, subject to the user’s

approval.

d. Include an interface Options feature that allows the user a choice of 2 (two)

color schemes to the interface, plus the ability to display a chart (graph) or two,

that illustrates the option sensitivity, as measured by your stock option

analytics procedures, to changes in the parameter values.

e. Include any other details required to achieve proper operation

Part D Open project

In this section you need to develop a VBA based solution to achallenging BUSINESS

task in the Excel environment.

Whilst the project is open, and can be based on any business discipline. The project

must be VBA based and located in a Module and/or UserForm moduleincluding the

name PartD.

You do not need approval of the topic, but you must include a “project proposal” in

your completed assignment. This proposal should describe the VBA components used,

and their application to the project’s development. However, this project cannot be too

easy to realised. Your effort and hard working is appreciated.

General

Write a Sub procedure to include "A 001" as an Application.Caption. Thiscode

should run when the Workbook is opened, and be Reset when the Workbook is

closed.

An Introduction worksheet should include:

A series of navigation buttons for the table of contents.

A list of references used

The page must be in good professional style

Innovation is encouraged, but keep within the scope of the VBA.