Results 1 to 4 of 4

Thread: How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

  1. #1
    Join Date
    Jan 2012
    Posts
    21

    How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

    I am looking at share prices across a five-year period and have them on a daily basis. If I want to graph them, there is little point in charting five-years worth of daily prices, as the graph loses clarity. I would like 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 such thing that would automatically convert daily data into longer time frames according to my design?

  2. #2
    Join Date
    Jul 2011
    Posts
    640

    Re: How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

    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.

  3. #3
    Join Date
    Jun 2011
    Posts
    635

    Re: How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

    Can you give more details on the data you have. You can not create values ​​of daily dates from months only. Unless you divide the value of your month by the number of days, and thus have an average per day. This is not ideal. In fact I have a planning application for different products, but it is given in months.

  4. #4
    Join Date
    Jun 2011
    Posts
    487

    Re: How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

    The Employees Hours of Work Manager application allows you to record daily input and output times of the employee and count the hours worked, any excess / lack of time, any recovery time and overtime. You can control the days of vacation, sick leave for the employee. The application consists of 14 worksheets plus a sheet with a brief introduction. There are two pages dedicated to the settings (Settings sheet Holidays and paper) which are connected for the remaining 12 sheets, one for each month of the year. You can use this to get easy result of what you are expecting.

Similar Threads

  1. Replies: 4
    Last Post: 04-01-2014, 10:47 AM
  2. Replies: 2
    Last Post: 17-02-2012, 02:06 PM
  3. How to convert Excel file data into XML code
    By Arif15 in forum Software Development
    Replies: 3
    Last Post: 20-10-2009, 07:21 PM
  4. Convert Excel data to HTML Table
    By Chalina in forum Software Development
    Replies: 2
    Last Post: 04-08-2009, 11:21 PM
  5. How to graph daily data in to monthly excel sheet
    By Sunny55 in forum Windows Software
    Replies: 2
    Last Post: 04-06-2009, 08:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,718,426,356.51023 seconds with 18 queries