Calculate monthly volatility from daily prices

[Costasg]

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?

Thanks in advance

[art297]

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.

[Costasg]

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?

[art297]

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.

[Costasg]

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

[art297]

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?

[Costasg]

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

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

[art297]

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

[Costasg]

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

[Costasg]

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?

[art297]

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;

[art297]

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;

[Costasg]

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.

[art297]

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.

[Costasg]

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