VBA 3

ysj

3.xlsx

Home >Business & Finance homework help >VBA 3

If statements

Test

10 points total

Previous Close

Daily Volume

Average Volume

Rule1

RULE2

Rule3

Create a UDF for each of the following Rules. Test your UDF with the data that appears in the table.

Ford

$ 9.58

$ 9.54

17,476,800

46,539,346

Daily price change measured from Previous Close to Close.

TWTR

$ 37.70

$ 34.42

10,757,870

26,252,670

a. One line if

RULE 1

4 points

MSFT

$ 111.86

$ 107.72

20,912,023

29,216,547

If daily price change is larger than 1% then buy.

DNKN

$ 73.14

$ 72.94

250,931

752,540

$ 151.57

$ 149.94

12,742,307

24,825,293

b. Block if

RULE 2

4 points

If daily price change is negative than sell

If daily price change is greater than 0, but less than 1/2 percent, then hold

If daily price change is greater than 1/2 percent than buy

c. Nested if

RULE 3

2 points

If daily price change is negative and daily volume is greater than average then sell but if daily volume is less than average then hold

If daily price change is greater than 0, but less than 1/2 percent and daily volume is less than average than hold; if daily volume is greater than average then sell

If daily price change is greater than 1/2 percent than buy unless daily volume is less than average, then sell

Hint: compute Close/Previous Close and Daily Volume/Average Volume

loops

10 points
a. simple loop		Write a UDF that will loop through the following range and find the sum of the squared numbers
	4
	5
	6
	7
	8
		<----------	Insert your custom function in B10
b. Create a new function by modifing your UDF from above such that the number of loops (which is the number of rows of data) is determined within the UDF code.
	Check your function by adding another row.
	4
	5
	15
	6
	7
	8
		<----------	Insert your custom function in B23

InputMsgBox

10 points
Create a simple routine to calculate the NPV of a project
Create a sub as follows:
1 pt	1. An input box to ask for the number of cash flows
1 pt	2. and input box to ask for the initial investment
1 pt	3. an input box to ask for the discount rate
2 pts	4. a loop with input boxes to ask for the cash flow for each year	Note, you will need to redim the cash flow array for 1 to number of cash flows specified in the first input box.
2 pts	5. a loop which will discount the cash flows (note that you can do this in the same loop of part 4)
2 pts	6. a msg box which gives the Net present value
1 pt	7. insert a button on this sheet to run the macro