Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dam
 
Posts: n/a
Default Seasonal factor graph

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 a

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
  #2   Report Post  
Jon Peltier
 
Posts: n/a
Default

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",Val ueRange))
=STDEV(IF(UPPER(TEXT(DateRange,"ddd"))="MON",Value Range))
=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/
_______

Dam wrote:

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 a

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to make a forecast for a highly seasonal business? [email protected] Excel Discussion (Misc queries) 1 January 25th 05 08:37 AM


All times are GMT +1. The time now is 01:20 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"