Seasonal factor graph

[Dam]

Dear newsgroup member,

I am looking for a way to calculate different seasonal factors in an
easy way on financial assets.

The data I have is the date (MM/DD/YYYY) as well as the daily price.

I define today percentage change as the difference between yesterday
price and todays one

What I am looking for are:

The mean, median, top and bottom 5% mean, standard deviation and the
percentage of positive occurence (% of the time where the asset rose) for:

The days of the week (Monday to Friday)
The day of the month with trading taking place (for example the first
day in January with trading, the second day,...)
The day of the month with trading taking place but this time starting
with the last day(last day of the month, ....)
The trading day of the year (1st,2nd,...) (day where I have a price)
the week of the year (first week,...)
the month of the year (January, Febraury,...)

And an easy way to graph it...

One of the problem is that the date varies depending on the asset as
some are not open a given day when other are,...

Really hope somebody can help me

Have a great day

Dam

 

[Jon Peltier]

This is possible using array formulas. If your dates are in a range named DateRange
and the values in ValueRange, you can find the average, standard deviation, and 5th
percentile for Mondays using:

=AVERAGE(IF(UPPER(TEXT(DateRange,"ddd"))="MON",ValueRange))
=STDEV(IF(UPPER(TEXT(DateRange,"ddd"))="MON",ValueRange))
=PERCENTILE(IF(UPPER(TEXT(DateRange,"ddd"))="MON",ValueRange),0.05)

These are array formulas, so they must be entered by holding down Ctrl+Shift while
pressing Enter. You can adjust the criteria to look at day of the month, day of the
year, month, etc.

For more information about array formulas, read these articles:

http://www.emailoffice.com/excel/arrays-bobumlas.html
http://cpearson.com/excel/array.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______