part a b c
docx
keyboard_arrow_up
School
Jaypee Institute of Information Technology *
*We aren’t endorsed by this school
Course
MISC
Subject
Information Systems
Date
Nov 24, 2024
Type
docx
Pages
4
Uploaded by ChefCrownStork26
Part A - Required Features
1. Calculate Daily Stock Returns
Sub CalculateDailyReturns()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
Cells(i, 3).Value = (Cells(i, 2).Value / Cells(i - 1, 2).Value) - 1
Next i
End Sub
This code assumes that the stock prices are in column B, and the returns will be calculated in column C.
2. Calculate 50 and 200-day Simple Moving Averages
Sub CalculateMovingAverages()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long
Dim N As Long ' Number of days for the moving average
N = 50 ' You can change this to 200 for the 200-day moving average
For i = N + 1 To LastRow
Cells(i, 4).Value = WorksheetFunction.Average(Range(Cells(i - N, 2), Cells(i, 2)))
Next i
End Sub
3. Identify Buy and Sell Signals
Sub IdentifySignals()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 3 To LastRow
If Cells(i, 4).Value > Cells(i, 5).Value And Cells(i - 1, 4).Value <= Cells(i - 1, 5).Value Then
' Buy Signal
Cells(i, 6).Value = "Buy"
ElseIf Cells(i, 4).Value < Cells(i, 5).Value And Cells(i - 1, 4).Value >= Cells(i - 1, 5).Value Then
' Sell Signal
Cells(i, 6).Value = "Sell"
End If
Next i
End Sub
4. Calculate Cumulative Returns and Generate Summary Statistics
Sub CalculateCumulativeReturns()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 6).Value = "Buy" Then
' Calculate cumulative returns for 1, 5, 10, and 30 days
https://www.coursehero.com/file/161636719/Lab-8-Submission-Interfacing-and-Programming-7-Segment-Displaysdocx/
This study source was downloaded by 100000854207054 from CourseHero.com on 11-10-2023 03:33:59 GMT -06:00
Cells(i, 7).Value = WorksheetFunction.Sum(Range(Cells(i, 3), Cells(i + 1, 3)))
' Add your code to calculate other holding periods
End If
Next i
End
Sub
Part
b
:
Step1: Structure of the Worksheet
First Worksheet:
- A1: Calculating the Moving Average Heading
- A3: Short-Term MA Period Label:
- B3: User-Inputted Short-Term MA Period
- A4: Long-Term MA Period Label:
- B4: User-Inputted Long-Term MA Period
- A6: Calculate (optional) button
- A8: Output: The results will be dynamically shown here
- A10: Extra information or instructions (optional)
2. Worksheet for Data:
Column A: Month
- Column B: Your data for the closing prices
- Column C: Computed Short-Term MA
- Column D: Computed Long-Term MA
Step 2: Equations
1. Determine the moving averages.
- Based on the user-input periods, compute the long- and short-term moving averages in the data spreadsheet using formulae.
The Short-Term MA, C2, is equal to AVERAGE(OFFSET(B2, 0, 0, $B$3, 1)).
D2: =AVERAGE(OFFSET(B2, 0, 0, $B$4, 1)) is the Long-Term MA.
2. Verify the Authenticity of Moving Averages:
- Use the formula in the main worksheet to determine the validity of the moving average periods that are given.
B6: =IF($B$4 > $B$3, "Valid", "Long-term MA must be longer than short-term MA") is how the validation is done.
https://www.coursehero.com/file/161636719/Lab-8-Submission-Interfacing-and-Programming-7-Segment-Displaysdocx/
This study source was downloaded by 100000854207054 from CourseHero.com on 11-10-2023 03:33:59 GMT -06:00
Step 3: Adaptive Results
=IF in A8"Short-Term MA: " & Data!C2 & " | Long-Term MA: " & Data!D2, B6) B6 = "Valid"
Section C:
It seems that you are asking for feedback on a VBA code and an Excel model. I can surely help you add comments to your Excel sheet
and VBA code, even if I am unable to directly give comments in a textbox or structure your code in an indented manner.
### Excel Sheet Remarks:
1. Guidelines Textbox: - Use the "Insert" option to add a textbox.
- Provide consumers with comprehensive instructions on how to use the model and any particular features.
2. Comments in Cells:* - Perform a right-click on a cell that requires further information.
- Choose "Insert Comment" and provide pertinent remarks.
Remarks in the VBA code:
1. Add remark Lines: - Include a remark line outlining the purpose of each section or block of code at the beginning of the code.
- Use comments to explain intricate computations or unique methods.
2. Commenting Code Blocks: - Add comments to code blocks to explain how they work.
CalculateMovingAverages() subroutine Using the periods entered by the user, this programme determines the moving averages for both
the short and long terms.
"Get user inputs."
ShortTermPeriod = Range("B3"), Dim shortTermPeriod As Integer.Worth
Dim longTermPeriod As Integer; range("B4") = longTermPeriod.Worth
Verify the validity of moving average periods.
Then, MsgBox "Long-term MA must be longer than short-term MA", vb, if longTermPeriod <= shortTermPeriodDeclarative Statement;
End If; Exit Sub
Last Sub
https://www.coursehero.com/file/161636719/Lab-8-Submission-Interfacing-and-Programming-7-Segment-Displaysdocx/
This study source was downloaded by 100000854207054 from CourseHero.com on 11-10-2023 03:33:59 GMT -06:00
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
3. Use Meaningful Variable Names: - Use descriptive variable names to help your code become self-explanatory.
- Include remarks outlining the significance of key factors.
User input for the short-term MA period is given via Dim shortTermPeriod As Integer.
The long-term MA period entered by the user is Dim longTermPeriod As Integer.
4. Indentation: - For improved readability, use consistent indentation.
Example Sub-RefSubprocessor()
The code block for condition 1 will be shown if condition 1 is met, and vice versa if condition 2 is met, and default code block will be
displayed if condition 2 is met. If Not Attached
https://www.coursehero.com/file/161636719/Lab-8-Submission-Interfacing-and-Programming-7-Segment-Displaysdocx/
This study source was downloaded by 100000854207054 from CourseHero.com on 11-10-2023 03:33:59 GMT -06:00