+ Reply to Thread
Results 1 to 10 of 10

How to calculate monthly performance of stocks using daily data

  1. #1
    Registered User
    Join Date
    05-21-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    10

    How to calculate monthly performance of stocks using daily data

    Hello,

    I am using excel to model an investing system.

    My system relies on measuring the performance of a stock over the past 10 months (i.e. the closing price on the last trading day of the latest month minus the closing price on the last trading day of the month 10 months ago).

    Obviously, this is easy to do using monthly price data. However, I need to use DAILY price data (so that I can calculate profit/loss on a daily basis).

    Using Daily price data, how do I get Excel to calculate the 10 month (or other duration) performance as described above?

    I can't simply use a fixed number of days (i.e. 200 days), as the number of days varies from month to month.

    Please help.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: How to calculate monthly performance of stocks using daily data

    Have you tried EDATE, EOMONTH, WORKDAY or any of the other date functions? And BTW is your profile up to date? There are newer functions that might help.

    A representative Excel file could help.

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Registered User
    Join Date
    05-21-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to calculate monthly performance of stocks using daily data

    Hello Dave,

    Thank you for your reply.

    I am not sure how I would use the functions that you listed, since the last trading day of each month is different from the last calendar day of the month.

    I have attached an Excel file with daily price data for the security SPY.

    On the file I have indicated the last trading day of January 2010 (the 29th) and the last trading day of November 2010 (the 30th) as examples of two dates that are 10 months apart. Next to the November date, I have explained the calculation that I am attempting to make (without hard-coding specific dates, but rather with Excel automatically using the last trading day of a given month).

    Your input would be much appreciated.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,773

    Re: How to calculate monthly performance of stocks using daily data

    Please try at G231 and copy down

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,000

    Re: How to calculate monthly performance of stocks using daily data

    In G231 then copy down

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Registered User
    Join Date
    05-21-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to calculate monthly performance of stocks using daily data

    Thank you to both of you for your replies and formulas. They both seem to work.

    So that I can learn, rather than blindly copy your formulas, could you (both of you) please walk me through your formulas in English so that I could understand them.

    I have tried breaking them up into smaller parts so that I can understand them, but I seem to be having a hard time.

    Again, thank you both for your help.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,000

    Re: How to calculate monthly performance of stocks using daily data

    Please Login or Register  to view this content.
    In the above
    Eomonth function will give end date of the month which is less by 10 months.
    Match function find the location of Eomonth. or Lesser date in the
    If Eomonth date isnot available lesser than Eomonth date is located.
    Index function gives corresponding E column value.
    Rest of the things are self explanatory.
    Is this ok.

  8. #8
    Registered User
    Join Date
    05-21-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to calculate monthly performance of stocks using daily data

    Hello kvsrinivasamurthy,

    Thank you for your explanation. It helps.

    One question: You mention that the MATCH function will find EOMONTH or lesser date. Let's say that we are in February, and the last trading day is the 26th. If I use the EOMONTH function and instruct it to go 10 months back (which would be April) and the last trading day of April was the 30th. 30 is greater than 26, will Excel default to the 26th of April and discard the remaining days (27 to 30)?

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,773

    Re: How to calculate monthly performance of stocks using daily data

    EOMONTH give the last day of any month, if it falls in April then it is 30th April.

  10. #10
    Registered User
    Join Date
    05-21-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to calculate monthly performance of stocks using daily data

    Got it.

    Thank you all for your help with this issue. It is much appreciated.

+ 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] 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
  2. VBA to get rolling correlation for specific time window, 30 years daily data, 225 stocks
    By jasonleewkd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2014, 06:43 AM
  3. Replies: 3
    Last Post: 10-01-2013, 05:05 AM
  4. Calculate daily targets given: month, monthly budget, daily indexes
    By Chuckyrp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2013, 03:30 AM
  5. Calculate monthly, quarterly, and yearly averages from daily data
    By jhound in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2013, 11:16 AM
  6. Calculate employee monthly performance
    By Georgiy in forum Excel General
    Replies: 2
    Last Post: 01-24-2013, 05:25 AM
  7. How Do You Calculate On-Going Daily/Monthly Interest?
    By StevenLAX in forum Excel General
    Replies: 1
    Last Post: 03-01-2005, 09:23 PM

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