ExcelBanter - Converting daily data into weekly, monthly and yearly data

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Converting daily data into weekly, monthly and yearly data (https://www.excelbanter.com/excel-discussion-misc-queries/200857-converting-daily-data-into-weekly-monthly-yearly-data.html)

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.

Answer: Converting daily data into weekly, monthly and yearly data

Yes, there is a way to convert daily data into weekly, monthly, and yearly data in Excel. Here are the steps:

First, make sure your data is organized in columns with the date in one column and the corresponding share price in another column.
To convert daily data into weekly data, you can use the

Code:

WEEKDAY

function to group the data by week. In a new column, use the formula

Code:

=WEEKDAY(A2)

where A2 is the cell with the date. This will return a number between 1 and 7, representing the day of the week. Then, use the formula

Code:

=AVERAGEIF(B:B,"="&WEEKDAY(A2),C:C)

where B:B is the column with the weekday numbers, C:C is the column with the share prices, and A2 is the first cell with the date. This will calculate the average share price for each week.
To convert daily data into monthly data, you can use the

Code:

EOMONTH

function to group the data by month. In a new column, use the formula

Code:

=EOMONTH(A2,0)

where A2 is the cell with the date. This will return the last day of the month for each date. Then, use the formula

Code:

=AVERAGEIF(B:B,"="&EOMONTH(A2,0),C:C)

where B:B is the column with the last day of the month, C:C is the column with the share prices, and A2 is the first cell with the date. This will calculate the average share price for each month.
To convert daily data into yearly data, you can use the

Code:

YEAR

function to group the data by year. In a new column, use the formula

Code:

=YEAR(A2)

where A2 is the cell with the date. This will return the year for each date. Then, use the formula

Code:

=AVERAGEIF(B:B,"="&YEAR(A2),C:C)

where B:B is the column with the years, C:C is the column with the share prices, and A2 is the first cell with the date. This will calculate the average share price for each year.
Once you have the weekly, monthly, and yearly data, you can create a chart using the new columns with the average share prices. Select the data and choose the chart type that best represents your data.

Let me know if you need any further assistance.

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.

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.

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.