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
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
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 "-" 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