Newsgroup: it.comp.lang.visual-basic
Titolo: Calcolo Autocorrelazione con VBA

Supponiamo di avere su un foglio excel dei dati cosi' predisposti:
A        B          C         ...            ....
1    C930    C1030
2    3,41     3,49  
3    3,52     3,48       
4    3,47     3,42 
5    3,45     3,50
6    3,52     3,57 
...    ...         ...
...    ...         ...
...    ...         ...
Vorrei scrivere del codice VBA che facesse la seguente cosa in seguito
al click di un command button
Sia Yield1 = ((B3/B2)-1)*100  e   Yield2 = ((C3/B3)-1)*100
Generalizzando Yield1(n) = ((B[n]/B[n-1])-1)*100   e 
Yield2(n) = ((C[n]/B[n])-1)*100
Vorrei che venisse calcolato e messo in una determinata cella il
coefficiente di correlazione tra Yield1(n) e Yield2(n) 
Ho delle difficolta'  con la sintassi da usare, soprattutto con
riferimento alla definizione di  Yield1(n) e Yield2(n)
Grazie
Ciao

> "SilverChair" [...]:

    Vado per approssimazioni: se tu scrivessi, per esempio in F1:
    =((INDICE(B2:B6;E1)/INDICE(B2:B6;E1-1))-1)*100
e in G1:
    =((INDICE(C2:C6;E1)/INDICE(B2:B6;E1))-1)*100
quindi in E1 l'indice (2, 3, 4, ...) non potrebbe andar bene? Per il VBA ci
pensiamo dopo.
--
Ciao.
Maurizio
--------
?SPQR(C)
 X
--------

pensiamo dopo.
Si va benissimo. In realta' pero' la cosa che ho intenzione di fare
l'ho gia' realizzata senza codice VBA, quindi su questo non ho
problemi.
Il mio intento ora e', al fine di prendere confidenza con il VBA,
quello di riscrivere il foglio che ho gia' realizzato con il solo
ausilio di tale linguaggio di programmazione.
In sintesi sul foglio devono esserci solo i dati e un bottone, tutto
il resto lo deve fare il codice.
Ciao 

> "SilverChair" [...]:

    Ok. Quindi il discorso sembrerebbe morire qui... Giusto?




Come morire qui.....non abbiamo neanche iniziato! ;-)
Non riesco a tradurre in linguaggio VBA cio' che invece ho realizzato
con le ordinarie formule excel.
Ad esempio, a tuo avviso, qual e' il modo piu' efficiente per
calcolare il coefficiente di correlazione di cui ti ho parlato
 (yield1 - yield2) facendo tutto sotto VBA?
Ciao

  (yield1 - yield2) facendo tutto sotto VBA?
    VBOH?
    A parte gli scherzi, piu' o meno sadici, esponi il problema in maniera
un po' piu' estesa, dettagliando lo scopo che ti proponi e senza dare per
scontate conoscenze specifiche sull'argomento. Per esempio io potrei anche
non sapere cosa sia il "coefficiente di correlazione" ma potrei sapere
perfettamente come fare un programma che lo calcola, se spieghi cosa intendi
e cosa vuoi fare.


e cosa vuoi fare.
Hai ragione, non sono stato proprio chiarissimo.
Dunque il coefficiente di correlazione lineare di Bravais-Pearson e'
un numero compreso tra 0 e 1 che indica il grado di interdipendenza
tra 2 variabili (piu' e' prossimo a 1 maggiore e' l'interdipendenza).
Excel ha un'apposita funzione: correlazione(matrice1,matrice2)
Il fatto e' che volendo fare tutto il lavoro "normalmente", senza
codice VBA, direi che l'operazione e' a dir poco banale.
Le motivazioni che mi spingono verso l'implementazione totale in VBA
sono in primo luogo la comprensione dello stesso, ma non e' da
trascurare un'altro aspetto consistente nel fatto che il progetto che
ti ho esposto nel mio primo post altro non e' che una prima, piccola
parte di cio' che dovra' essere il lavoro finale. La parte restante
pero' e' grosso modo una ripetizione della prima, per cui compresa la
prima sara' agevole estendere il lavoro a tutto il resto.
Pertanto l'utilizzo del codice VBA mi consentirebbe di ridurre le
dimensioni del lavoro il quale al termine dovra' ritornare non solo la
correlazione tra c930 e c1030, ma anche quella tra tutte le altre
combinazioni di prezzo e ordinarle in un certo modo.
Per rendere l'idea, ti dico solo che il foglio che ho attualmente
implementato e' composto da 8 fogli excel collegati tra loro, ognuno
dei quali si compone di numerosi sheet per un ammontare di 130 Mbyte.
Le combinazioni tra prezzi sono diverse centinaia!!!
Con il VBA riuscirei invece a fare tutto con l'ausilio di un solo
foglio.
Comunque, per non complicare troppo la questione, io direi di
procedere con ordine ed affrontare un passo per volta.
Ed il primo passo e' per l'appunto il calcolo del coefficiente di
correlazione (lo stesso presente tra le formule fornite da excel) tra
yield1 e yield2 cosi' come definiti nel precedente post e che mi
sembra tu abbia perfettamente compreso.
In conclusione occorre calcolare via VBA (richiamando se e' possibile
la formula excel "correlazione(x,y)", altrimenti ti fornisco io la
formula stessa, il coefficiente di correlazione yield1- yield2;
ribadisco che la cosa che io non so come rendere in VBA consiste
proprio nella definizione dei 2 range dinamici yield2 e yield1.
Se non sono stato ancora abbastanza chiaro fammi sapere.
Ciao

> "SilverChair" [...]:

    Sai chi ha scritto le parole seguenti?
"Mi sto rendendo conto che, almeno per me, il problema principale della
programmazione, ovvero la cosa che piu' ti fa perdere tempo, non e' il
pensare e impostare il programma stesso, ma e' l'andare a cercarsi di
volta in volta la sintassi e le notazioni giuste da utilizzare per
tradurre cio' che hai in mente in un modo comprensibile dal pc.
In ogni caso per imparare, a mio avviso, non c'e' altra strada che
iniziare a scrivere autonomamente qualcosa e quindi imparare sul
campo, risolvendo i problemi via via che ti si presentano......magari
con l'aiutino di qualcuno."
    Sta diventando un bel romanzo epistolare questo 3d!
    I protagonisti: un tizio che non capisce ed uno che fa finta di non
capire. Ma non e' mai perfettamente chiaro quale dei due si chiami Maurizio
e quale SilverChair. Quindi l'attenzione del lettore e' sempre desta...
    :-))))
    Ed ora parliamo seriamente! ANALISI. Ecco la parola che non avevo voluto
evocare nell'altro mio post. _Analisi_ non vuol dire letteratura,
trattatistica, dissertazione, confidenze, sfoghi, prese di posizione
ideologiche. Ma _Analisi_ non vuol dir neppure sintassi, notazione,
linguaggi.
    Io, troppo velatamente, volevo spingerti verso l'_Analisi_ del problema:
la sola cosa che possa portarti a scrivere un programma. A seconda dei casi
puoi farla a mente, sul margine del quotidiano o in bella con diagrammi di
flusso e ammennicoli varii. Ma la *devi* fare. E la devi fare tu, magari
facendoti aiutare dal ng. Nel tuo scritto, perdona, non ce n'e' traccia ed
io, un po' per celia, un po' per non morir (o meglio per non diventar
vecchio rispondendoti) ti imploro di rivedere la questione alla luce (al
faro) della parola ANALISI. Serve aiuto? Allora parliamo di questo. Non di
altro.


altro.
Scusami Maurizio, io, anche se mi piacerebbe, non sono un
programmatore professionista e non so cosa tu esattamente intenda per
ANALISI. Fermo restando comunque che cio' che voglio ottenere ce l'ho
ben chiaro in mente, tant'e' vero che l'ho gia' realizzato per altra
via.
L'aiuto che io ti/vi sto chiedendo riguarda la scrittura in modo
comprensibile dal compilatore VBA di un codice che calcoli un
coefficiente di correlazione tra 2 insiemi di variabili che io ho
chiamato yield1 e yield2.
Ora il problema e': dalla descrizione nel mio primo post hai capito
cosa voglio ottenere? Sei riuscito a visualizzare la struttura dei
dati iniziali, nonche' la definizione di yield1 e yield2? E tutta qui
l'essenza del problema.......una volta definiti in VBA l'insieme di
valori da interpretare come yield1 e quello da interpretare come
yield2 poi si tratta solo di dirgli di calcolare un semplice coeff. di
correlazione tra i 2.
Se non ti e' chiaro cosa intendo per yield1 e yield2 allora riprovo ad
esprimermi in altri termini. 
Pero' ho bisogno di capire cosa esattamente non ti e' chiaro nella mia
iniziale descrizione.
Per semplificare cerco di sintetizzare i 3 ordini di problemi che
possono non essere stati da me sufficientemente chiariti.
1) Struttura iniziale dei dati in "Foglio1" ( dati dai quali si dovra'
procedere alla estrazione di yield1 e yield2 per il successivo calcolo
del coeff. di correl., il tutto con il solo ausilio del VBA)
2) Cosa intendo per yield1 e yield2
3) qual e' la formula matematica del coeff. di correlazione
Ciao e grazie 

 3) qual e' la formula matematica del coeff. di correlazione
--


 3) qual e' la formula matematica del coeff. di correlazione
Silver, Maurizio te l'ha già detto in tutti i modi: devi spiegare,
passo-passo e come se parlassi a un poppante, cosa sono, cosa fanno e cosa
ottengono queste cose.
Per esempio:
Ho questi dati..., devo fare questi calcoli..., devo ottenere questi
risultati..., ci sono queste eccezioni e casi particolari...
Io non so cosa sia il coefficiente di correlazione, nè mi interessa saperlo.
Io faccio gestionali da qualche decennio. Al loro interno c'è anche la
tenuta della contabilità in partita doppia e la stesura del bilancio.
Tu pensi che io saprei tenere la contabilità o stendere un bilancio?
Ma nemmeno per sogno e, a proposito di sogni, dormo tranquillo lo stesso.

risultati..., ci sono queste eccezioni e casi particolari...
E' quello che credevo di aver fatto nel mio primo post, ma
evidentemente per me era tutto cosi' semplice e automatico che ho dato
per scontato moltissime cose. 
Pero' neanche mi avete detto cosa esattamente non avevate capito.
In realta' concettualmente la cosa da fare e' di una semplicita'
disarmante.
Anche la traduzione in VBA sapevo che era abbastanza semplice ed
infatti alla fine credo di esserci riuscito anche io che e' la seconda
volta nella mia vita che apro un editor VBA.
Ora che ho scritto (almeno spero) il codice giusto mi e' sorto un
dubbio.
Vi sottopongo il problema e questa volta sara' certamente tutto chiaro
in quanto vi fornisco a titolo di esempio il foglio excel con codice
incorporato.
Questo e' il link:
http://web.tiscali.it/defil/BestACFVBA.zip
Cliccando sul commandbutton si ottiene il calcolo del coefficiente di
correlazione tra yield1 e yield2 , gia' precedentemente definiti
(nella cella A746 si ha il valore del coefficiente ottenuto dal codice
VBA; nella cella N748 c'e' lo stesso coefficiente calcolato senza
VBA).
Non e' importante conoscere il significato matematico-economico di
tale coefficiente; prendetelo come in un indice di qualche cosa, un
semplice numero compreso tra 0 e 1.
Come controprova della bonta' del codice ho appunto scritto la stessa
procedura di calcolo utilizzando le normali funzioni excel; si trova
nelle colonne N ed O. 
Ripeto queste due ultime colonne non sono necessarie in quanto tutta
la procedura e' scritta in VBA, ma serve solo da esempio per rendervi
ancora piu' chiaro cio' che volevo fare ed inoltre serve come conferma
che il valore ottenuto nella cella A746 sia uguale a quello nella
cella N748.
Ecco, il mio  problema ora e' questo: i 2 valori non coincidono,
quando invece dovrebbero, dato che per il calcolo ho richiamato la
stessa funzione di excel (Excel.WorksheetFunction.Correl)
Dovrei pensare che il codice che ho scritto e' sbagliato, ma non
riesco a trovare l'errore.
Ci potete dare un'occhiata?
Thanks


 Ci potete dare un'occhiata?
Volentieri, ma per l'ennesima volta di codice non pubblichi una virgola che,
comunque, sarebbe sintatticamente scorretta.
Cosě ti allontani...



Il codice e' nel foglio excel.
Lo devo pubblicare anche sul NG?
Ciao

> "SilverChair" [...]:

    Prima di tutto:
        Option Explicit
e, perche' venga impostato per ogni nuovo modulo:
    Strumenti > Opzioni
    Editor
    [V] Dichiarazione di variabili obbligatoria
        Private Sub CommandButton1_Click()
            Dim tdc930(10000)   As Double
            Dim tdc1030(10000)  As Double
            Dim i               As Integer
            Dim j               As Integer
            Dim yield1(10000)   As Double
            Dim yield2(10000)   As Double
            Dim miovett1(10000) As Double
            Dim miovett2(10000) As Double
            Dim n               As Double
            n = WorksheetFunction.Max(Range("A5:A2000"))
            For i = 1 To n - 1
                tdc930(i) = Cells(i + 2, 3)
                tdc1030(i) = Cells(i + 2, 4)
                If i > 1 Then
                    yield1(i) = ((tdc930(i) / tdc930(i - 1)) - 1) * 100
                    yield2(i) = ((tdc1030(i) / tdc930(i)) - 1) * 100
                End If
                miovett1(i) = yield1(i)
                miovett2(i) = yield2(i)
            Next i
            Cells(n + 5, 1) = WorksheetFunction.Correl(miovett1, miovett2)
        End Sub
n = WorksheetFunction.Max(Range("A5:A2000"))
    Perche' parti da A5?
    Il risultato non cambia ma e' bene che tu sia cosciente che nel range
A5:A2000 e' compresa la cella A746 che contiene il
valore -0,0363105124045545, apparentemente incoerente con quanto sopra.
    Considera questo codice:
With Range("A3")
    Debug.Print .End(xlDown).Value - .Value + 1
End With
    E questo:
With Range("A3")
    Debug.Print Range(.Cells(1), .End(xlDown)).Rows.Count
End With
OUTPUT (di entrambi):
 741
ma c'e' una bella differenza di significato! Chiaro, no?
    Ora che sappiamo che gli elementi sono 741 possiamo dimensionare i
vettori con maggior precisione:
Dim tdc930()   As Double
Dim tdc1030()  As Double
Dim yield1()   As Double
Dim yield2()   As Double
Dim miovett1() As Double
Dim miovett2() As Double
Dim n          As Long
With Range("A3")
    n = Range(.Cells(1), .End(xlDown)).Rows.Count
End With
ReDim tdc930(1 To n)
ecc...
    Per ora mi fermo.


valore -0,0363105124045545, apparentemente incoerente con quanto sopra.
Hai ragione. Comunque quel numero "incoerente" e' il famigerato
coefficiente di correlazione che potendo assumere come massimo valore
1 non influisce sul calcolo del nostro n (a trovarlo un coefficiente
pari ad 1....si diventerebbe milionari in dollari in poche settimane,
ma purtroppo e' impossibile)
Devo riconoscere che il tuo codice e' molto piu' elegante ed efficace;
chiedo scusa per la rozzezza del mio scrivere, ma veramente sono le
primissime volte che mi cimento nella programmazione.
Pertanto ti ringrazio perche' con i tuoi suggerimenti imparo
tantissimo sia in termini di stile che di nuove funzioni ;-)
Ad esempio nella lezione odierna ho imparato with, Debug.Print, xlDown
(utilissimo). 
Un curiosita' veloce,veloce; ma l'utilita' di Dim e ReDim e' solo
quella di consentire una efficiente allocazione della memoria o c'e'
qualcosa di piu'? Insomma senza Dim e Redim il programa funziona lo
stesso ma succhia piu' memoria giusto?
Continuando con il programma in esame ho continuato dove ti sei
interrotto.
Noto comunque lo stesso problema che avevo riscontrato prima. Le celle
A746 e N748 contengono valori che non coincidono esattamente quando
invece dovrebbero dato che si tratta in tutto e per tutto della stessa
cosa......cambia solo il metodo usato.
Perche'?
Ecco il codice completo:
Option Explicit
Private Sub CommandButton1_Click()
Dim tdc930()   As Double
Dim tdc1030()  As Double
Dim yield1()   As Double
Dim yield2()   As Double
Dim miovett1() As Double
Dim miovett2() As Double
Dim n          As Long
Dim i          As Long
With Range("A3")
    n = Range(.Cells(1), .End(xlDown)).Rows.Count
End With
ReDim tdc930(1 To n)
ReDim tdc1030(1 To n)
ReDim yield1(1 To n)
ReDim yield2(1 To n)
ReDim miovett1(1 To n)
ReDim miovett2(1 To n)


 Perche'?
    Bella domanda. Boh? Mo' vediamo... ;-)

    Trovato.
  ReDim tdc930(1 To n)
  ReDim tdc1030(1 To n)
  ReDim yield1(1 To n - 1)
  ReDim yield2(1 To n - 1)
  ReDim miovett1(1 To n - 1)
  ReDim miovett2(1 To n - 1)
  For i = 1 To n
    tdc930(i) = Cells(i + 2, 3)
    tdc1030(i) = Cells(i + 2, 4)
    If i > 1 Then
      yield1(i - 1) = ((tdc930(i) / tdc930(i - 1)) - 1) * 100
      yield2(i - 1) = ((tdc1030(i) / tdc930(i)) - 1) * 100
      miovett1(i - 1) = yield1(i - 1)
      miovett2(i - 1) = yield2(i - 1)
    End If
  Next


  ReDim miovett2(1 To n - 1)
Perfetto :-)
Ma come hai fatto a trovare la soluzione? Intendo dire, quando si ha
un problema come questo, la cosa da fare e' quella di premere
continuamente F8 e seguire passo passo l'evoluzione del codice
(osservando i valori delle variabili, ecc.)?
E' cosi' o c'e' qualche altro stratagemma? Tu come hai fatto?
Mi scuso per quest'altra domanda da iperprincipiante......spero solo
che possa essere d'aiuto a qualche altro lettore.
 
 Ritornando al mio programma, ora che il primo passo e' compiuto e
perfettamente funzionante, diciamo che a questo punto inizia il
difficile.
Pero' devo dire che sto scoprendo quanto affascinante sia la
programmazione e quindi non mi dispiace sbatterci la testa su. 
Mi ci sto davvero appassionando ;-)
In estrema sintesi si tratta di eseguire lo stesso identico
procedimento con tutte le combinazioni di prezzo. 
Il progetto e' alquanto ambizioso e soprattutto e' un autentico
rompicapo...e si, avete idea di quante combinazioni sono?...il calcolo
non e' difficile;-)
Naturalmente limitando il tutto ai giorni:  ieri, oggi, domani
altrimenti dubito che 10 cpu in parallelo riuscirebbero a portare a
termine il lavoro in tempi umani.
Dal mio punto di vista il rompicapo sta nel fatto che io non voglio
ripetere la stessa procedura per ogni combinazione: in questo modo ne
verrebbe fuori un codice lunghissimo, ma la cosa sarebbe alquanto
agevole.
Cio' che vorrei fare e' il creare un codice quanto piu' compatto
possibile con una serie di cicli for....next uno all'interno
dell'altro........almeno credo sia questa la tecnica.
E' questa la cosa difficile,  proprio perche' estremamente articolata.
Inizio a lavorarci un po' su........in caso di dubbi mi risentirete
prossimamente su questi schermi ;-)
Infine un grosso ringraziamento va a Maurizio. Se ci fossero piu'
persone come te, permeate da un profondo spirito di solidarieta' e
pronte ad aiutare il prossimo non senza qualche doverosa ramanzina di
tanto in tanto ;-), il mondo sarebbe un pochino migliore.
Non voglio ne' leccare, ne' essere mieloso, blea'...non e' il mio
stile, ma mi sembra un dato oggettivo ed inconfutabile.
Bye.

 Infine un grosso ringraziamento va a Maurizio.
[...]
    Che dire... grazie per le belle parole e grazie anche per avermi
sopportato. :-)
