Calculate monthly volatility from daily prices

Solved
Frequent Contributor
Posts: 75

Calculate monthly volatility from daily prices

Hello again,

I have daily stock prices and I need to calculate the monthly volatility. I attach a sample file that describes the process in excel, though since there are too many stocks working in excel is too much time concuming.

Is there a way to do that in SAS having all stocks in one file?

Accepted Solutions
Solution
‎08-09-2011 10:50 AM
PROC Star
Posts: 8,163

Re: Calculate monthly volatility from daily prices

Yes, there were definitely some things that had to be changed in order to match the results.  I think that the following calculates exactly what you were doing in Excel.  The main differences from what I had originally proposed are: (1) using the log10 function rather than the log function; (2) multiplying the differences by 100; and (3) using the corrected rather than the uncorrected sums of squares.

data need;

set have;

array vars(*) a1--a100;

array diff(100);

yrmon=put(DATE,yymmd.);

do i=1 to dim(vars);

diff(i)=(log10(vars(i))-log10(lag(vars(i))))*100;

end;

run;

proc means data=need mean uss nway noprint;

var diff1--diff100;

class yrmon;

output out=want (drop=_

mean= css= /autoname;

run;

data want;

set want;

array diffs(*) diff1_CSS--diff100_CSS;

do i=1 to dim(diffs);

if diffs(i) gt 0 then

diffs(i)=diffs(i)**0.5;

end;

run;

All Replies
PROC Star
Posts: 8,163

Calculate monthly volatility from daily prices

Should be easy, but one would have to know what you would want or need in the output file.  You are only creating a new variable, namely the log of the current price minus the log of the previous price and then calculated the sum of squares (USS) for those values.  That could be as simple as a datastep (creating a month and year variables from date and creating a normalized price change variable (i.e., log(price)-log(lag(price))) and a proc means with USS as the desired stat and using year and month as class variables.

Frequent Contributor
Posts: 75

Calculate monthly volatility from daily prices

Hi art297,

thanks for your reply. It should be easy, though I am new to SAS and I do not have sufficient knowledge on it. So far I have a table with the stocks' log diferrences (100 stocks) and another table with the monthly averages of them.

What I want to do is to create a new table where the variables will be the log price minus the monthly average. Keep in mind tha the log prices are daily and the average is for each month, so all prices of january 2000 will be minus the average of this month, the prices of february 2000 with the average of this month and so on. Below is how the data looks like:

My log prices table:

 DATE A1 A2 A100 31/1/2000 0.256 0.5424 0.564 1/2/2000 0.321 0.1223 0.446 2/2/2000 0.453 0.41554 0.265 3/2/2000 0.1524 0.151 0.456 4/2/2000 0.2045 0.1656 0.355

My average table has prices of the form:

 DATE A1 A2 A100 JAN2000 0.152 0.156 0.256 FEB2000 0.235 0.245 0.3565 MAR2000 0.456 0.654 0.265

What I want to get is :

 DATE A1 A2 A100 31/1/2000 0.256-0.152 0.5424-0.156 0.564-0.256 1/2/2000 0.321-0.235 0.151-0.245 0.456-0.3565 2/2/2000 0.453-0.235 0.1656-0.245 0.355-0.3565

(numbers only for reference)

So each price will be substracted by the given average of the specific month.

Any suggestions?

PROC Star
Posts: 8,163

Calculate monthly volatility from daily prices

It would be easy to generate your desired file, but what I was mentioning is that the file wouldn't even be needed to achieve the final result I think you want.

You could simply take the first file and run something like:

data have;

informat date ddmmyy10.;

input DATE A1 A2 A100;

yrmon=put(DATE,yymmd.);

cards;

31/1/2000 0.256 0.5424 0.564

1/2/2000 0.321 0.1223 0.446

2/2/2000 0.453 0.41554 0.265

3/2/2000 0.1524 0.151 0.456

4/2/2000 0.2045 0.1656 0.355

;

proc means data=have mean uss;

var a1--a100;

class yrmon;

run;

You can also add an output statement with that same proc and produce a file that contains the calculations which could then be merged with your input data file.  But, if all you really want is the result, the above proc means will calculate your desired numbers.

Frequent Contributor
Posts: 75

Calculate monthly volatility from daily prices

Sorry for bothering you again but I am really newbie into SAS and I don't understand how some things work. I imported my data from an excel file which has 100 stocks and some thousands of observations. Normally I should skip the cards part that u have written.

I am a bit confused with the yrmon varibale

since the data is imported through the manager and already exists as a table, If I write

data have;

input DATE A1-A100;

proc means data=have mean uss;

var a1--a100;

run;

I get the overall USS for each variable, not the monthly,

I tried with yrmon=put(DATE,yymmd.); after the input statement and later in the proc means statement as u wrote but it doesn't work.

How I could modified this in order to make it work?

I just want to produce a file I can export with the desired results.

Sorry If I make any silly questions but as I said I am really new into SAS.

thanks for your understanding

PROC Star
Posts: 8,163

Calculate monthly volatility from daily prices

You said that you imported the file from excel.  In the resulting SAS file, what kind of field is the field "DATE" (i.e., character or numeric), and what does at least one of the entries look like?

Frequent Contributor
Posts: 75

Calculate monthly volatility from daily prices

The date has the form of   21JUN1996 (format: DATE9.)

and the entries have the form 1.0234345595 (fomat: BEST12.)

PROC Star
Posts: 8,163

Calculate monthly volatility from daily prices

If it is really a numeric field with a date9. format, the code I originally suggested would have worked.  If the file were called have, you would have been able to submit the following:

data need;

set have;

yrmon=put(DATE,yymmd.);

run;

proc means data=need mean uss;

var a1--a100;

class yrmon;

run;

Run the following on your file to find out if the field is really character or numeric:

proc contents data=have;

run;

Art

Frequent Contributor
Posts: 75

Calculate monthly volatility from daily prices

It works!!!

The only problem now is than in my output table  (the one I want to export) there is no USS, only min, max, mean, std and N. How can i fix this?

Thanks

Frequent Contributor
Posts: 75

Calculate monthly volatility from daily prices

Something else, I compared the calculations from SAS with those that I did manually for some stocks and they have totally different results. Maybe the model I use measure s differently the volatility.

So, if I want to to do what I said above, I mean from the daily prices of each month substract the average of the specific month how I would do it?

PROC Star
Posts: 8,163

Calculate monthly volatility from daily prices

Did you convert your data points to log as in your original example?  If not, try it with:

data need;

set have;

array vars(*) a1--a100;

yrmon=put(DATE,yymmd.);

do i=1 to dim(vars);

vars(i)=log(vars(i));

end;

run;

proc means data=need mean uss nway;

var a1--a100;

class yrmon;

output out=want (drop=_

mean= uss= /autoname;

run;

PROC Star
Posts: 8,163

Calculate monthly volatility from daily prices

Try it with the following syntax:

proc means data=need mean uss nway;

var a1--a100;

class yrmon;

output out=want (drop=_

mean= uss= /autoname;

run;

Frequent Contributor
Posts: 75

Calculate monthly volatility from daily prices

I tried both ways but I still get diferrent reasults. Maybe it is the methodology of the paper that I am using different.

What I need to do is: let Pt the price of stock today.

1. convert prices: [log(Pt)-log(Pt-1)]*100 (let's call it X)

2.get the average of each month (lets call it x)

3. substract each X by the average. Xt-x (let's call it y)

4.miltiply y*y.

5.Finally get the average of the above for each month to get the monthly volatility.

PROC Star
Posts: 8,163

Calculate monthly volatility from daily prices

Don't think I can be of further help then.  In your 2:30pm post, yesterday, you indicated that you were starting with a table that contained the log differences.  Thus, there shouldn't be any need to take the log of the values or use the lag function in order to obtain the difference amounts.

The calculation of the average (i.e., mean) and sum of squared differences from the mean (i.e., uss) are standard calculations and wouldn't differ between SAS and Excel.  You could be using different log functions, or the paper might be taking the volatility calculation one step further and dividing the uss by n-1 and obtaining the variance.

Frequent Contributor
Posts: 75

Calculate monthly volatility from daily prices

That's ok art297, many thanks for your help! Maybe SAS calculates the avarege of the whole time series and substracts it from the observations instead of calculating each month's seperetaly. I will try to find out why I get different results.

Many thanks again

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 18 replies
• 4477 views
• 6 likes
• 2 in conversation