Successivamente, premendo il pulsante "Grafico dati storici Automatico" , vorrei ottenere in sequenza il carico degli stessi csv, sempre con riferimento ai ticker presenti da A6 in poi di fo Isin, con una successione continua e con questa logica di lavoro:
- apri csv n.1 ,
- applica la macro di creazione del grafico,
- attendi qualche secondo,
- cancella dati e cancella grafico,
- riprendi dal secondo CSV e cosi via .
Dov'è il problema ? la sequenza di carico dei file CSV e relativi grafici in sequenza non è corretta = non funziona a dovere e il grafico non si completa con la elaborazione delle medie.
Da notare che la macro che mette in grafico i dati funziona correttamente per un singolo Ticker, va in tilt quando tento di applicare la sequenza .
Scopo di tutto ciò e avere una rappresentazione grafica - medie comprese - dei dati storici presenti nei CSV, in sequenza .
Sotto la macro incriminata e la cartella con i file di riferimento .
https://ufile.io/in7ewtuz
- Codice: Seleziona tutto
Option Explicit
Sub ImportCSVFile() ' automatico
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
Dim Ticker As String
Dim fileName As String
Dim uR As Integer
Dim lR As Long
Dim i As Integer
Dim Dest As Range
Dim Rng As Integer
Dim cartella As String
Dim wbOpen As Workbook
Dim NomeFoglio, uRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Ws1 = Sheets("DatiY")
Set Ws2 = Sheets("Storico_CSV")
Set Ws3 = Sheets("Isin")
Const strPath As String = "C:\Test\"
' questo è il ciclo che scansiona gli isin prenti in A6 e segg. di fo Isin
'With Ws3
uR = Ws3.Cells(Rows.Count, "A").End(xlUp).Row
For i = 6 To uR
Ws2.Unprotect
Ws3.Unprotect
Ws2.Range("K1:R1000").ClearContents
cancella_grafico_E_dati
Ticker = Ws3.Range("A" & i)
If Ticker = "" Then Exit Sub
fileName = Dir(strPath & Ticker & ".csv") ' percorso completo
Set wbOpen = Workbooks.Open(strPath & fileName)
uRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("A1:G" & uRow).Copy
NomeFoglio = ActiveSheet.Name
Windows("1_ETF multi isin_Ya dDiretto (6)_Auto_test.xlsm").Activate ' cambiare
Ws1.Range("$a$1").PasteSpecial
Ws1.Range("$I$1") = NomeFoglio
Ws1.Columns("B:g").NumberFormat = "0.000"
Ws1.Columns("A:G").AutoFit
'-----------------------------------------------------------------------------------
'copia1
Dim uR2 As Long
Ws2.Unprotect
With Ws2
.Range("K:Q").ClearContents
End With
uR2 = Ws1.Range("A" & Rows.Count).End(xlUp).Row
Ws1.Range("A1:G" & uR2).Copy
Ws2.Range("K1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Ws1.Range("i1").Copy Destination:=Ws2.Range("A1")
Ws2.Range("A1").Value = Ws1.Range("I1").Value
Call RowKiller
Call AnalyzeData
'---------------------------------------------------------------------------------------------------
Application.Wait (Now + TimeValue("00:00:05"))
Add_Chart
Application.Wait (Now + TimeValue("00:00:10"))
wbOpen.Close
fileName = Dir
i = i + 1
Next i
' End With
'MsgBox ("Finito")
Ws2.Protect
Set Ws1 = Nothing
Set Ws2 = Nothing
End Sub
Private Sub RowKiller()
Dim Col As String, n As Long, i As Long
Dim Y As New DataObject ' macro copia data solo testo , no formula
Sheets("Storico_CSV").Unprotect
Sheets("Storico_CSV").Activate
Range("$j$3").Clear
Y.SetText [V2].Value ' dato di origine
Y.PutInClipboard
Sheets("Storico_CSV").Range("J$3").Select
Y.GetFromClipboard
ActiveCell = Y.GetText
Col = "L"
n = Cells(Rows.Count, Col).End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, Col).Value = "null" Then
Cells(i, Col).EntireRow.Delete
End If
Next i
Sheets("Storico_CSV").Protect
End Sub
Private Sub AnalyzeData()
Dim i As Integer
Dim LastRow As Integer
Dim avReturn As Double
Dim stDev As Double
Dim vrnc As Double
Sheets("Storico_CSV").Unprotect
On Error Resume Next
LastRow = Sheets("Storico_CSV").UsedRange.Row - 1 + Sheets("Storico_CSV").Range("U2")
LastRow = Sheets("Storico_CSV").Cells(Rows.Count, 11).End(xlUp).Row
Sheets("Storico_CSV").Range("S1") = "Daily Returns" 'ex J1
Sheets("Storico_CSV").Range("U1") = "# Data" 'ex L1
Sheets("Storico_CSV").Range("U2") = LastRow 'ex L2
For i = 3 To LastRow
Sheets("Storico_CSV").Range("S" & i) = (Sheets("Storico_CSV").Range("O" & i - 1) - Sheets("Storico_CSV").Range("O" & i)) / Sheets("Storico_CSV").Range("O" & i - 1)
Next i
avReturn = Application.WorksheetFunction.Average(Sheets("Storico_CSV").Range("S2:S" & LastRow))
stDev = Application.WorksheetFunction.StDev_P(Sheets("Storico_CSV").Range("S2:S" & LastRow))
vrnc = Application.WorksheetFunction.Var_P(Sheets("Storico_CSV").Range("S2:S" & LastRow))
Sheets("Storico_CSV").Range("H2") = avReturn
Sheets("Storico_CSV").Range("H3") = stDev
Sheets("Storico_CSV").Range("H4") = vrnc
Resume
Sheets("Storico_CSV").Protect
End Sub
Private Sub Add_Chart()
Dim cht As Object
Dim LastRow As Long
Dim Rng1 As Range
Dim shDest As Worksheet 'foglio in cui creare il grafico
Set shDest = Worksheets("Storico_CSV")
Application.EnableEvents = False
Application.ScreenUpdating = False
shDest.Unprotect
shDest.Activate
With ActiveSheet
LastRow = .Range("K" & .Rows.Count).End(xlUp).Row
Set Rng1 = .Range("L2:L" & LastRow & ", K2:K" & LastRow)
End With
shDest.Range("B3") = Application.WorksheetFunction.CountA(Sheets("Storico_CSV").Range("K:K"))
Set cht = ActiveSheet.ChartObjects.Add(Left:=Range("A7").Left + 7, Width:=800, Top:=Range("A7").Top, Height:=400)
With cht
.Chart.SetSourceData Source:=Rng1
.Chart.ChartType = xlLine
.Chart.FullSeriesCollection(1).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 112, 192)
.Transparency = 0
.Solid
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 112, 192)
.Transparency = 0
End With
ActiveChart.PlotArea.Select
End With
With cht
.Chart.FullSeriesCollection(1).Trendlines.Add
.Chart.FullSeriesCollection(1).Trendlines(1).Select
With Selection
.Type = xlMovingAvg
.Period = 200
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(112, 48, 160)
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.75
End With
Selection.Format.Line.Style = msoLineSingle
Application.CommandBars("Format Object").Visible = False
End With
With cht
.Chart.FullSeriesCollection(1).Trendlines.Add
.Chart.FullSeriesCollection(1).Trendlines(2).Select
With Selection
.Type = xlMovingAvg
.Period = 50
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.75
End With
Selection.Format.Line.Style = msoLineSingle
End With
With cht
.Chart.Legend.Select
Selection.Position = xlLeft
.Chart.Legend.IncludeInLayout = False
.Chart.Legend.Select
Selection.Position = xlTop
.Chart.Legend.IncludeInLayout = True
End With
With cht
.Chart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "0.00"
End With
With cht
.Chart.HasTitle = True
.Chart.ChartTitle.Text = Range("B1")
Sheets("Storico_CSV").Protect
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Sub cancella_grafico_E_dati()
Dim myChart As ChartObject
Dim currSheet As Worksheet
Dim tmpName As String
Set currSheet = Sheets("Storico_CSV")
currSheet.Unprotect
If ActiveSheet.ChartObjects.Count > 0 Then
For Each myChart In currSheet.ChartObjects
tmpName = myChart.Name
myChart.Delete
Next
End If
Dim Ws1 As Worksheet
Set Ws1 = Sheets("DatiY")
Ws1.Range("A:G").Clear
End Sub
metto il file tua versione, ricordati di comperare quando il prezzo supera