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
|
Private Sub CommandButton1_Click()
Dim short_code As String
Dim line As String
Dim Cible As Range
Dim lng As Integer
Dim cln As Integer
Dim eday As Integer
Dim emonth As Integer
Dim eyear As Integer
Dim sday As Integer
Dim smonth As Integer
Dim syear As Integer
Set Cible = ActiveCell
parametres.Hide
Application.ScreenUpdating = False
short_code = TextBox1.Value
eday = TextBox2.Value
emonth = TextBox3.Value - 1
eyear = TextBox4.Value
sday = TextBox5.Value
smonth = TextBox6.Value - 1
syear = TextBox7.Value
'Extrait les données
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & short_code & "&d=" & emonth & "&e=" & eday & "&f=" & eyear & "&g=d&a=" & smonth & "&b=" & sday & "&c=" & syear & "&ignore=.csv", _
destination:=Cible)
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
'Convertis les données
Cible.EntireColumn.TextToColumns destination:=Cible, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
Cible.EntireColumn.AutoFit
' Supprime lignes inutiles
Cible.Offset(0, 2).EntireColumn.Delete
Cible.Offset(0, 2).EntireColumn.Delete
Cible.Offset(0, 2).EntireColumn.Delete
'Calcul de rentabilité jounalière
lng = Cible.End(xlDown).Row - 1
cln = Cible.End(xlToRight).Column
'Remplace . par , dans les nombres
Range(Cells(1, 2), Cells(lng, cln)).Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'Calcul de la rentabilité journalière
Cells(1, cln + 1) = "Rnt journ"
For i = 2 To lng - 1
Cells(i, cln + 1) = (Cells(i, cln) / Cells(i + 1, cln)) - 1
Next
Cells(lng + 1, cln + 1).Formula = "=AVERAGE(" & Cells(2, cln + 1).Address & ":" & Cells(lng - 1, cln + 1).Address & ")"
Cells(lng + 1, 1) = "DAILY AVERAGE"
'Calculs de variance
Cells(1, cln + 2) = "Variance"
For i = 2 To lng - 1
Cells(i, cln + 2) = ((Cells(i, cln + 1) - Cells(lng + 1, cln + 1))) ^ 2
Next
Cells(lng + 1, cln + 2).Formula = "=AVERAGE(" & Cells(2, cln + 2).Address & ":" & Cells(lng - 1, cln + 2).Address & ")"
Cells(lng + 2, cln + 2).Formula = "=252 *" & Cells(lng + 1, cln + 2).Address
Application.ScreenUpdating = True
End Sub |
