+ Reply to Thread
Results 1 to 2 of 2

Trying to calculate weekly & monthly returns using daily share price data

  1. #1
    Registered User
    Join Date
    05-21-2020
    Location
    Ottawa, Canada
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    1

    Trying to calculate weekly & monthly returns using daily share price data

    Hi all,

    I am trying to calculate weekly and monthly returns using daily share price data.

    In its simplest form, the calculations should be:
    1. Current Week / Last Week - 1
    2. Current Month / Last Month - 1


    I added in columns for Year, Month, Week, and Day to facilitate using some IF formulas paired with VLookups but got stuck.

    There seem to be two complicating factors:


    The data spans multiple years (i.e. there is not only one week 2, there is a week 2 in every year of data)
    Not every week has the same number of days (holidays, etc.)

    I've attached some sample cells. Any help you could give me would be hugely appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Trying to calculate weekly & monthly returns using daily share price data

    Hello 4MA8 and Welcome to Excel Forum.
    It is always helpful for the person asking the question to include a few manually calculated results so that contributors have something with which to compare the results of their formulas/code.
    That said I feel that the following formulas yield the results you are looking for:
    Weekly close and adjusted close:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Monthly close and adjusted close:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF(OR statement to calculate percentage of hours for daily, weekly, or monthly tasks
    By DjJazzyJeff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2016, 12:39 PM
  2. [SOLVED] Formula to Group analytical data imputed daily into daily weekly and monthly analysis.
    By tianasamour in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-13-2015, 06:10 PM
  3. Replies: 1
    Last Post: 04-13-2015, 04:38 PM
  4. how to calculate weekly and monthly percentage returns from daily figures
    By bensittingdown in forum Excel Formulas & Functions
    Replies: 32
    Last Post: 10-15-2014, 05:02 PM
  5. Replies: 3
    Last Post: 10-01-2013, 05:05 AM
  6. Calculate Totals weekly and Monthly from daily Figures
    By mfjanoo in forum Excel General
    Replies: 3
    Last Post: 03-05-2012, 03:06 PM
  7. Replies: 1
    Last Post: 07-11-2008, 05:57 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1