A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Converting daily data into weekly, monthly and yearly data



 
 
Thread Tools Display Modes
  #1  
Old August 31st 08, 07:39 PM posted to microsoft.public.excel.misc
CEGavinMcGrath
external usenet poster
 
Posts: 13
Default Converting daily data into weekly, monthly and yearly data

I am looking at share prices across a five-year period and have them on a
daily basis. If I want to graph them, however, there is little point in
charting five-years' worth of daily prices, as the graph loses clarity. I
would like, therefore, to be able to see long-term graphs in, say, weeks or
months worth of data. Is there a way that I can run a Macro, or some such
thing, would automatically convert daily data into longer time frames
according to my design?

Many thanks in advance.
Ads
  #2  
Old August 31st 08, 09:31 PM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,856
Default Converting daily data into weekly, monthly and yearly data

Well, you haven't exactly described what your design is. I presume you
would have a code for each share and maybe a description/name, and
also a date and a price, but you may have other fields as well.

How would you like to combine daily data into monthly? As an average?
If so, you can put 1st Jan 2008 in a cell (say M1) and 1st Feb 2008 in
the next cell (M2) and then fill these down so you get 1st of each
month thereafter. Then you could use this array* formula:

=AVERAGE(IF((MONTH(date_range)=MONTH(M1))*(YEAR(da te_range)=YEAR(M1))*(share_range="code")),price_ra nge))

where date_range, share_range and price_range are whatever you use in
your file, and code is the share you are interested in. Then you can
copy the formula down for as many dates as you have in column M.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
subsequently edit/amend the formula you must use CSE again.

Hope this helps.

Pete



On Aug 31, 7:39*pm, CEGavinMcGrath
> wrote:
> I am looking at share prices across a five-year period and have them on a
> daily basis. *If I want to graph them, however, there is little point in
> charting five-years' worth of daily prices, as the graph loses clarity. *I
> would like, therefore, to be able to see long-term graphs in, say, weeks or
> months worth of data. *Is there a way that I can run a Macro, or some such
> thing, would automatically convert daily data into longer time frames
> according to my design?
>
> Many thanks in advance.


  #3  
Old September 1st 08, 09:39 AM posted to microsoft.public.excel.misc
CEGavinMcGrath
external usenet poster
 
Posts: 13
Default Converting daily data into weekly, monthly and yearly data

Pete,

Many, many thanks. I appreciate your help.

Regards, Gavin.

"Pete_UK" wrote:

> Well, you haven't exactly described what your design is. I presume you
> would have a code for each share and maybe a description/name, and
> also a date and a price, but you may have other fields as well.
>
> How would you like to combine daily data into monthly? As an average?
> If so, you can put 1st Jan 2008 in a cell (say M1) and 1st Feb 2008 in
> the next cell (M2) and then fill these down so you get 1st of each
> month thereafter. Then you could use this array* formula:
>
> =AVERAGE(IF((MONTH(date_range)=MONTH(M1))*(YEAR(da te_range)=YEAR(M1))*(share_range="code")),price_ra nge))
>
> where date_range, share_range and price_range are whatever you use in
> your file, and code is the share you are interested in. Then you can
> copy the formula down for as many dates as you have in column M.
>
> *An array formula needs to be committed using the key combination of
> Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this
> correctly then Excel will wrap curly braces { } around the formula
> when viewed in the formula bar - do not type these yourself. If you
> subsequently edit/amend the formula you must use CSE again.
>
> Hope this helps.
>
> Pete
>
>
>
> On Aug 31, 7:39 pm, CEGavinMcGrath
> > wrote:
> > I am looking at share prices across a five-year period and have them on a
> > daily basis. If I want to graph them, however, there is little point in
> > charting five-years' worth of daily prices, as the graph loses clarity. I
> > would like, therefore, to be able to see long-term graphs in, say, weeks or
> > months worth of data. Is there a way that I can run a Macro, or some such
> > thing, would automatically convert daily data into longer time frames
> > according to my design?
> >
> > Many thanks in advance.

>
>

  #4  
Old September 1st 08, 10:11 AM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,856
Default Converting daily data into weekly, monthly and yearly data

You're welcome, Gavin - thanks for feeding back.

Pete

On Sep 1, 9:39*am, CEGavinMcGrath
> wrote:
> Pete,
>
> Many, many thanks. *I appreciate your help.
>
> Regards, Gavin.
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
sales data how i can list weekly,monthly yearly etc..etc... Abdul Gaphoor Excel Worksheet Functions 1 May 1st 06 12:14 PM
spreadsheet for tracking reports daily, monthly, yearly and graph Kompare Excel Worksheet Functions 2 April 6th 06 09:28 PM
Collecting weekly and monthly totals from daily data Kasper Excel Worksheet Functions 0 January 12th 06 08:02 PM
Converting Weekly Data into Monthly Averages Kaine Excel Worksheet Functions 7 February 27th 05 11:13 PM
Converting Weekly Data into Monthly Averages Kaine Excel Discussion (Misc queries) 2 February 25th 05 09:03 AM


All times are GMT +1. The time now is 04:31 PM.


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