Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Calculate monthly volatility from daily prices

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 08-05-2011 08:53 AM
(7812 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

18 REPLIES 18

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.