1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
| Option Base 1
Option Explicit
Function fnReturns(stockPrice As Variant) As Variant
'variables
Dim Returns() As Variant 'matrix that will contain the returns for the three stocks (result of the function fnReturns)
Dim n_returns As Integer 'nb of returns to calculate for each stock
Dim n As Integer 'nb of rows of the table in wks ex2_data
Dim m As Integer 'nb of columns of the table in wks ex2_data
Dim i As Integer 'loop variable
Dim j As Integer 'loop variable
Dim a As Integer
'get the nb of rows and columns of the table in wks ex2_data and assign the values to the variables n and m
n = Worksheets("ex2_data").Range("B2:D13").Rows.Count
m = Worksheets("ex2_data").Range("B2:D13").Columns.Count
'assign to the variable n_returns the nb of returns to calculate using the variable n
n_returns = (n * m) - 3
'Resize the matrix Returns taking into account the nb of returns to calculate for the three stocks
ReDim Returns(n_returns) As Variant
' Calculate the returns in the matrix Returns for the three different stocks :
' - You need to use two loops for..Next
'- one for the rows and one for the columns of your matrix
'recall: to compute a return with divide a price with the previous one and substract 1 (returns(t)=price(t)/price(t-1)-1)
For i = 2 To n
For j = 1 To m
Returns(i) = stockPrice(i, j) / stockPrice(i - 1, j) - 1
Next j
Next i
'Return the matrix Return
fnReturns = Returns
End Function
'------------------------
Sub procReturns()
'wks variables
Dim wsD As Worksheet 'wks variable for the wks named ex2_data
Dim wsR As Worksheet 'wks variable for the wks named ex2_results
'variant variables
Dim stockPrice As Variant 'contains the stock prices of the three stocks (input of the function fnResults)
Dim fn_result As Variant ' contains the result of the function fnReturns (output)
Dim outputReturns As Variant
'numerical variables
Dim nb_rows As Integer
Dim bn_col As Integer
'Assign the worksheets to the wks variables
Set wsD = Worksheets("ex2_data")
Set wsR = Worksheets("ex2_results")
'assign the nb of rows and columns of the input table to the variable nb_row and nb_col
nb_rows = wsD.Range("B2:D13").Rows.Count
bn_col = wsD.Range("B2:D13").Columns.Count
'Assign the values of the range of stock prices to the variable stockPrice
'Hint: resize a range using nb_rows and nb_col in order to get the range of cells then apply _
the property value to assign the values of the range to the variable stockPrice
ReDim stockPrice(nb_rows, bn_col)
stockPrice = wsD.Range("B2:D13").Value
'Assign to the variable outputReturns the value of the range of cells colored in orange in the wks "ex2_results"
'Hint: use again the property resize and the property value
ReDim outputReturns(nb_rows - 1, bn_col)
outputReturns = wsR.Range("B2:D12").Value
'Assign to the variable fn_result the value returned by the function fnReturns _
with the variable StockPrice as the argument of the function (the variable StockPrice cannot be a range object!)
fn_result = fnReturns(stockPrice)
outputReturns = fn_result
wsR.Range("B2:D12").Value = outputReturns
End Sub |
VBA -Calculer le rendement et mettre résultats dans une autre feuille
Répondre avec citation
Envoyé par chou2best
ce n'est pas cette ligne que j'ai rajouté ... la copie est dans le mauvais sens ...!
Partager