# How to calculate monthly performance of stocks using daily data

1. ## 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.

2. ## 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 “Close window”
• click “Submit Reply”
The file name will appear at the bottom of your reply.

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

Hello Dave,

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. ## Re: How to calculate monthly performance of stocks using daily data

Please try at G231 and copy down

Formula:
`Please Login or Register  to view this content.`

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

In G231 then copy down

``Please Login or Register  to view this content.``

6. ## 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. ## 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. ## 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. ## 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. ## 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.

##### Users Browsing this Thread

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

#### 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