PDA

Visualizza versione completa : OverFlow, perchè ?


Gianca53
06-11-2017, 19.24.22
Ciao, giusto per non farmi mancare nulla, mi sono imbattuto in un nuovo problema , ovvero sto tentando di applicare una macro , perfettamente funzionante nel file originale (Yahoo stock etc) ma nel mio tentativo di applicazione mi dà un errore 6 di Overflow.
Ho provato a passare un malware, ho verificato il PC con Memory test , ma l 'errore resta e non so più che fare. Chiedo quindi il vs aiuto. Allego entrambi i file .

I file sono caricati i due formati diversi, zip e rar, per problemi di "ingombro".

le formule che mi interessano possono essere calcolate anche per via normale , con Excel, ma in questo caso mi sorgerebbe un altro problema ovvero definire un intervallo dinamico per il calcolo della deviazione standard e/o della varianza , ovvero con numerosità del campione soggetto a variazioni .

ps Ho provato anche a modificare alcune dim da Double a Long, pensando a un qualche vincolo che ne impediva il funzionamento, ma nulla da fare.:inkaz:

Alexsandra
06-11-2017, 20.54.53
Io ho aperto il file "yaho ......"
sono andata nel foglio "Data" e ho cancellato tutto.
dal foglio "parameters" ho lanciato la macro tramite il pulsante "Get data fron Yahoo" e mi ha riempito le celle del foglio "data"

poi ho cliccato sul pulsante "analyze data" e mi ha riempito la colonna J e le prime 2 celle della colonna L senza darmi nessun errore.

Noto però che hai delle chiamate SET, questo comando alloca una porzione di memoria per la variabile che segue la parola chiave SET e non la distruggi a fine routine.
ti consiglio di inserire prima della chiamata End Sub la dicitura per eliminare l'allocazione di memoria. Per esempio, hai una chiamata

Set dataSheet = Sheets("Data")

prima di uscire dalla routine distruggi tutto con

Set dataSheet = Nothing

ripeti questo comando per tutte le chiamate SET che hai nelle routine, poi riprova. L'overflow causato è di sicuro dovuto a un uso ripetuto della routine senza eliminare dalla memoria i dati.

forse.

Gianca53
06-11-2017, 22.43.07
Adesso provo a modificare il mio file "TestDevSTD", che è quello che mi dà problemi. Sorry se sono stato poco chiaro nella richiesta.
In quel file troverai un elenco dati statico, che non si aggiorna perchè non ho riportato la macro di "carico", comunque su questo elenco vorrei applicare la macro Analyzedata.

ZeroKelvin
06-11-2017, 23.00.41
Ciao.
Forse la soluzione è molto più banale
Sub AnalyzeData()
Dim i As Integer
Dim LastRow As Integer
Dim avReturn As Double
Dim stDev As Double
Dim vrnc As Double

LastRow = Sheets("Data").UsedRange.Row - 2 + Sheets("Data").UsedRange.Rows.Count
Sia i che LastRow sono dichiarate Integer ( valore compreso fra -32.768 e 32.767) ma, dal momento che sono destinate a contenere un valore di riga (le righe in un foglio sono 1048576) è molto probabile che siano queste variabili a provocare l'overflow.
Prova a dichiararle come Long.

Gianca53
07-11-2017, 08.20.03
Provato, nulla da fare si impianta qui :
Sheets("Data").Range("J" & i) = (Sheets("Data").Range("E" & i - 1) - Sheets("Data").Range("E" & i)) / Sheets("Data").Range("E" & i - 1)
con il solito messaggio di overflow

LoryOne
07-11-2017, 08.28.16
Se dividi per 0, l'overflow è diretta conseguenza.

LoryOne
07-11-2017, 11.21.23
Concentriamoci su LastRow del file YahooStockQuotes...
Hai scritto:

LastRow = Sheets("Data").UsedRange.Row - 2 + Sheets("Data").UsedRange.Rows.Count

In realtà, l'ultima riga non può che essere l'insieme di righe in uso, ossia:

LastRow = Sheets("Data").UsedRange.Rows.Count

Le righe in uso sono giustamente 1008, non 1007

A questo punto, prova a cancellare l'ultima riga selezionandola interamente e poi premendo cancella.
Rieseguendo il codice, il valore sarà sempre 1008.
Com'è possibile, visto che alla riga 1008 non c'è nulla ? Addirittura il valore della J1008 pari ad 1 !?!
Riseleziona la riga 1008 e questa volta eliminala.
Riesegui ed il valore di LastRow sarà effettivamente 1007

Gianca53
07-11-2017, 15.54.27
Ma a te funziona, senza piu errore di overflow ? Io =impedito ma testardo, non ci sono mica riuscito .

Alexsandra
07-11-2017, 17.59.45
Adesso provo a modificare il mio file "TestDevSTD", che è quello che mi dà problemi..

:devil::devil:

Il problema dell'overflow è il calcolo della riga, in quel file l'ultima riga scritta è la 1284 se continui con il codice e trovi delle righe vuote ti rimanda errore perchè vuoi che faccia dei calcoli su valori vuoti.
Modifica la riga di codice
LastRow = Sheets("Storico_CSV").UsedRange.Row - 1 + Sheets("Storico_CSV").Range("U2")

con
LastRow = Sheets("Storico_CSV").Cells(Rows.Count, 11).End(xlUp).Row
(colonna K) e non ti darà più errore.

Te ne riporterà un'altro alle righe

Sheets("Storico_CSV").Range("avReturn") = avReturn
Sheets("Storico_CSV").Range("stDev") = stDev
Sheets("Storico_CSV").Range("vrnc") = vrnc

con range devi inserire un riferimento di cella e non una variabile.

che cosa sono per te avreturn etc....

LoryOne
07-11-2017, 18.52.31
Ma a te funziona, senza piu errore di overflow ? Io =impedito ma testardo, non ci sono mica riuscito .

Aggiungi un debug.print nel ciclo for...next.
Controlla il valore della variabile i e delle celle fino a quando ti va in overflow: L'overflow è quasi certamente dovuto all'operazione algebrica su quei valori, ma tu effettua un debug.print prima del calcolo, così da poter verificare i valori di tutte le variabili prima che si possano eseguire calcoli su di esse.
La finesta di debug si visualizza con Ctrl+G nell'editor VBA.

Gianca53
07-11-2017, 19.06.22
Ciao, risolto, finalmente ! Funziona tutto e non fa piu scherzi con l'overflow, complimenti per la tua "mossa" di cambiare la colonna di riferimento, è stata la pensata :act: risolutiva.

Option Explicit
Sub AnalyzeData()
Dim i As Integer
Dim LastRow As Integer
Dim avReturn As Double
Dim stDev As Double
Dim vrnc As Double

'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("Data").Range("J" & i) = (Sheets("Data").Range("E" & i - 1) - Sheets("Data").Range("E" & i)) / Sheets("Data").Range("E" & i - 1)
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

End Sub


allego anche il file , se qualcuna si interessa di titoli azionari puo essere utile assieme ai file del mio 3d precedente

Gianca53
07-11-2017, 20.13.03
Grazie a tutti per l'aiuto. Alla prossima ;)

Gianca53
08-11-2017, 16.34.36
Aggiornamento.
Capita che i dati scaricati abbiano qualche "buco" quindi la macro si impiantava per errore 13 , ho perciò inserito un controllo errore .
Option Explicit
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
Range("H2,H3,H4").Clear.txt

'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("Data").Range("J" & i) = (Sheets("Data").Range("E" & i - 1) - Sheets("Data").Range("E" & i)) / Sheets("Data").Range("E" & i - 1)
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
If Err.Number = 13 Then ' inserire apici x messaggio non visibile
MsgBox "Valore errato o nullo"
End If

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



Ps . Ho tentato di aggiungere il file ma per problemi di ingombro il sistema lo ha rifiutato > 135 kb. Cosa fare in questi casi ? giusto per mia info. grazie

Alexsandra
08-11-2017, 20.42.47
Io ho provato varie volte e non mi rimanda nessun errore.
Per "Buco" nei dati intendi forse che ci sia qualche riga vuota??
se è così meglio gestire il ciclo IF con una condizione diversa tipo

SE "ciccio" = empty Then
salta una riga (i = i+1)
end if

Ho usato questo codice
Option Explicit
Sub AnalyzeData()
Dim i, LastRow As Integer
Dim avReturn, stDev, vrnc As Double

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

End Sub

Gianca53
08-11-2017, 21.13.35
Grazie Alexandra, l'esempio era nel file che non sono riuscito ad allegare, comunque ciò che succede con nuovi dati è simile a :

03/10/2017 3.51600 3.52600 3.47800 3.50000 3.50000 84424
04/10/2017 3.52000 3.64000 3.50000 3.60000 3.60000 528431
05/10/2017 3.60000 3.63000 3.57800 3.63000 3.63000 105169
06/10/2017 null null null null null null null null null
09/10/2017 3.61200 3.64000 3.60000 3.63000 3.63000 100570
10/10/2017 3.60000 3.62800 3.59400 3.61400 3.61400 59571
11/10/2017 3.62000 3.62200 3.56200 3.57400 3.57400 145759
12/10/2017 3.59600 3.60600 3.52000 3.52000 3.52000 152681
13/10/2017 3.56400 3.63000 3.52800 3.60600 3.60600 100436
16/10/2017 3.61800 3.62800 3.57200 3.60400 3.60400 110429
17/10/2017 3.62400 3.65600 3.57200 3.63200 3.63200 266612
18/10/2017 3.65000 3.65000 3.60200 3.63200 3.63200 101378


come vedi c'è la data ma non il valore, con frequenza / ubicazione del tutto random, quindi dovrei identificare tutto il range dei valori sino all'ultima riga utile e ragionare in termini di righe da saltare. E' questo il tuo pensiero?

Gianca53
09-11-2017, 15.44.46
Si, mi rispondo da solo.:) Però non ho fatto il salto riga ma ho preferito la cancellazione delle righe vuote perchè mi sono accorto che altrimenti il calcolo veniva alterato per eccesso ovvero, quando l'istruzione di calcolo della macro analyze trova una riga vuota mi piazzava un bel + 1,0 nella colonna "S" del daily Returns e conteggiava tale riga come valida . Quindi con grave errore di calcolo, tanto più se si considera che i valori di per se' sono infinitesimi se non negativi.

Rimuovendo il rigo vuoto i conti tornano perfettamente.

Ho usato questa macro inserita in un modulo e richiamata prima di avviare la macro Analyze.
Sub RowKiller()
Dim col As String, N As Long, i As Long
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
End Sub

LoryOne
09-11-2017, 19.07.04
In realtà, puoi fare tutto nel ciclo for...next
Imposti 2 sole variabili:
A=Sheets("Storico_CSV").Range("O" & i - 1)
B=Sheets("Storico_CSV").Range("O" & i )
La formula è semplicemente (A-B)/B
Devi decidere se:
- una delle due vale null
- tutte e due valgono null
In entrambi i casi, iteri la variabile i (i = i+1) prima del next

Gianca53
10-11-2017, 20.23.53
Grazie Loryone , ma preferirei continuare con la versione del post 16 per un altro motivo, ovvero vorrei contare quante volte si presenta l'errore 13 . Ad esempio se si presenta per n > 3 dati mancanti si "rigetta" tutto il calcolo successivo.

Gianca53
11-11-2017, 13.12.45
Me la sono cavata con un sistema "misto ":
in v2 ho inserito =CONTA.SE($L2:$L2000;"null")
nella macro Rowkiller ho inserito :
Dim Y As New DataObject ' macro copia dato, no formula
Range("H5").Clear
Y.SetText [V2].Value ' cella di origine
Y.PutInClipboard
Sheets("Storico_CSV").Range("H5").Select 'destinazione
Y.GetFromClipboard
ActiveCell = Y.GetText

che trasferiscono il solo valore, senza formula, in H5.