Questo articolo vi spiegherà nel dettaglio come costruire un foglio di calcolo in Excel per calcolare la volatilità storica di un titolo. Avrete inoltre a disposizione gratuitamente il file.
Elementi necessari per calcolare la VS in Excel
- Dati Storici (prezzi di chiusura giornalieri della nostra azione o future) – esistono molti siti dove trovarli gratuitamente ad esempio Yahoo Finance. In questo articolo raccontiamo come fare.
- Software Excel o similare open source
Le funzione Excel utilizzate
Il calcolo della volatilità storica a dispetto di quello che possa sembrare non è troppo complicato. Useremo solo le seguenti funzioni di Excel:
LN = logaritmo naturale – per calcolare i rendimenti logaritmici giornalieri
DEV.ST.C = deviazione standard del campione – per calcolare la deviazione standard di questi rendimenti
RADQ = radice quadrata – per annualizzare la volatilità
Non preoccupatevi se non avete familiarità con alcuni di essi. È a questo che serve questa pagina. Oltre a queste funzioni ci sono solo le basi – moltiplicazione, divisione, formule di copia ecc.
Quindi cominciamo. Inizieremo da zero – basta aprire un nuovo foglio di lavoro Excel vuoto.
Step 1: Dati storici sul file
La volatilità storica è calcolata dai prezzi di chiusura storici giornalieri. Pertanto, il primo passo è quello di inserire i prezzi storici nel nostro foglio di calcolo.
In questo esempio calcolerò la volatilità storica delle azioni Microsoft (simbolo MSFT), utilizzando i dati di Yahoo Finance dal 1 Gennaio 2021 al 31 Dicembre 2021.
Se non avete dati, volete usare Yahoo Finance e non sapete come trovare e scaricare i dati da lì, ho creato un tutorial dettagliato.
Metteremo i dati nelle colonne A (data) e B (prezzo di chiusura). Usate la riga 1 per l’intestazione, così sapremo quale colonna fa cosa più tardi quando aggiungeremo altre colonne.
Incollate i dati nelle righe 2 e seguenti.
Incollate i dati nelle righe 2 e seguenti.
Step 2: Calcolare i ritorni logaritmici
La volatilità storica (almeno il metodo di calcolo più comune che stiamo usando qui) è calcolata come deviazione standard dei rendimenti logaritmici. Dobbiamo quindi prima calcolare questi rendimenti logaritmici (chiamati anche rendimenti composti in modo continuo) per ogni giorno (riga). Lo faremo nella colonna C.
È molto semplice: il rendimento logaritmico giornaliero è il logaritmo naturale (ln) del rapporto tra il prezzo di chiusura e il prezzo di chiusura del giorno prima. Matematicamente:
=LN(B3/B2)Dove la cella B3 è il prezzo di chiusura del giorno corrente e la cella B2 il prezzo di chiusura del giorno precedente.
Step 3: Calcolare la deviazione standard
Il passo successivo è calcolare la deviazione standard di questi rendimenti giornalieri.
La deviazione standard è la radice quadrata della varianza, che è la deviazione quadratica media dalla media. Excel ha una funzione integrata per la deviazione standard. In realtà ci sono due funzioni, perché ci sono due tipi di deviazione standard: la deviazione standard della popolazione e la deviazione standard del campione.
Per il calcolo della volatilità storica useremo la deviazione standard del campione e la formula di Excel per questo è DEV.ST.C.
Questa formula prende solo un argomento ed è il riferimento alle celle per le quali vogliamo calcolare la deviazione standard. Funziona con un minimo di due celle e con un massimo di tutte quelle che il vostro computer può gestire. Calcoleremo la deviazione standard per ogni giorno, utilizzando una finestra mobile – un periodo di n giorni consecutivi che termina il giorno per il quale stiamo calcolando la deviazione standard.
Che periodo n utilizzare?
Quanti giorni (n) includere nella nostra finestra mobile? Questa è la grande decisione che dovete prendere quando calcolate la volatilità storica. Viene spesso chiamato periodo di volatilità storica ed è usato in modo simile alle medie mobili e ad altri indicatori di analisi tecnica.
Se si sceglie un periodo più breve, la volatilità storica risultante rifletterà più da vicino l’azione di mercato più recente, ma nel tempo oscillerà di più (sarà più “volatile”). Al contrario, se si sceglie un periodo lungo, sarà più stabile, ma forse potrebbe non riflettere sufficientemente gli sviluppi più recenti. C’è un trade-off da accettare tra le due.
Se non ne avete idea di dove partire, vi consiglio quelli che uso spesso io:
21 giorni di trading = 1 mese di calendario
63 giorni = 3 mesi
252 giorni = 1 anno
Occasionalmente potrei anche guardare la volatilità storica a 10 giorni se qualcosa di interessante sta accadendo nel mercato, ma non andrei più in basso di così.
Quindi decidiamo di usare 21 giorni per il nostro esempio. Calcoleremo ogni deviazione standard usando gli ultimi 21 rendimenti nella colonna C. La prima riga dove possiamo farlo è la riga 23, dove useremo i 21 rendimenti nelle celle da C3 a C23. La formula della deviazione standard nella cella D23 sarà:
=DEV.ST.C(C3:C23)
Step 4: Annualizzare la volatilità storica
In realtà, abbiamo già calcolato una serie di valori di volatilità storica, perché è quello che sono le deviazioni standard. L’unico problema è che rappresentano la volatilità storica di 1 giorno (perché sono stati calcolati dai rendimenti giornalieri). Quindi il passo finale del nostro calcolo è quello di convertire la volatilità a 1 giorno in volatilità annualizzata, che è molto più comune e molto più utile.
Per convertire la volatilità da giornaliera ad annuale è necessario moltiplicarla per la radice quadrata del numero di giorni di trading all’anno.
Perché i giorni di negoziazione? Perché abbiamo usato una serie di giorni di trading (fine settimana e giorni festivi non inclusi).
Perché la radice quadrata? Perché la volatilità (come la stiamo usando ora) è la deviazione standard e la deviazione standard è la radice quadrata della varianza, dove il numero di giorni entra effettivamente nel calcolo.
Quanti giorni di trading ci sono in un anno? Questo dipende dal mercato con cui state lavorando, dato che diversi paesi e diverse borse osservano diverse festività. Inoltre, anche per lo stesso paese e la stessa borsa il numero di giorni di trading varia di anno in anno. Una buona media a lungo termine per i mercati statunitensi è di 252 giorni di trading all’anno, il dato che userò. Se avete una storia di dati più lunga e volete essere molto precisi, potete contare il numero medio di giorni di trading all’anno direttamente dai vostri dati. Ma per ora atteniamoci a 252.
Calcoleremo la volatilità storica annualizzata nella colonna E, che sarà uguale alla colonna D moltiplicata per la radice quadrata di 252. In Excel, la formula per la radice quadrata è RADQ e la nostra formula nella cella E23 sarà:
=D23*RADQ(252)
Copiamo di nuovo questa formula in tutte le altre celle sottostanti. Possiamo anche formattare le colonne C, D, E come percentuali.
La serie che abbiamo calcolato nella colonna E è la volatilità storica che cercavamo.
Il dato può anche essere facilmente graficato:
Possibile miglioramento
Possiamo fare in modo che il foglio supporti la lunghezza variabile del periodo di volatilità storica, che l’utente inserirebbe in qualche cella designata invece di cambiare tutte le formule di deviazione standard. Suggerimento per i lettori interessati: utilizzare la funzione SCARTO di Excel.
