+ Reply to Thread
Results 1 to 14 of 14

Correlation - How to make two columns match dates relative to balance

  1. #1
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Correlation - How to make two columns match dates relative to balance

    Ok so column A and Column F have dates in (see spreadsheet)

    I want to see what the correlation is between column "H" Balance and column "C" Balance.

    I know the dates need to be equal on both the columns irrespective of whether or not there was a positive or negative gain that given day. So system 1 may have a positive "Percentage Gain" on the 06/04/2014 where as system 2 does not have any positive gain or negative loss. So therefore there still needs and entry for the 06/04/2014 with "0.0%" within the corresponding percentage gain box. The balance would of course for system 2 remain unchanged.

    I do not know how to do this but I do know that this accurate comparison is required in order to carry out the correlation analysis.

    Any brain bots want to have a look at the spread-sheet, please feel free
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Correlation - How to make two columns match dates relative to balance

    Ok so column A and Column F have dates in (see spreadsheet)
    The first thing I noticed is that only some of the entries in column A are dates. Some are text that look like dates. I also noticed that a few dates are the nonsensical 1/0/1900, which I expect is incorrect. I executed a quick Text to columns command to convert the text dates to serial dates, but I wasn't sure what would be expected for the 1/0/1900 dates (probably either the date before or the date after). The first step in this will probably be to convert all of column A to real dates.

    From there, a lot of doing a correlation analysis probably depends on how you define (mathematically) correlation. I'm not a financials, statistics, or the "statistics of financial data" expert, so there are probably "standard" ways of measuring correlation that are common in the field. If you explain what "correlation analysis" means, we should be able to help you program that into the spreadsheet.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Correlation - How to make two columns match dates relative to balance

    Oooh thanks for your response

    Ok so first off I have re-uploaded the correct spread-sheet to be looking at (sorry!). Although this doesn't fix the date issues.

    Secondly the dates are CLOSE DATE. Each row represents a "trade" (financial markets trading) outcome (%) that corresponds to the given date.

    I want to see how positively or negatively correlated system 1 is against system 2. In order to do this I want to compare the "Balance" columns. If there are multiple inputs on the same date, then it needs to be a sum total for that date. The correlation is done by the DAILY balance. If there are no "trades" on a given day that System 1 traded on, then system 2's previous day balance will just carry over...

    Hope this makes sense?

    As for the different types of correlation, I am not actually that clued up with that tbh :P
    Attached Files Attached Files

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Correlation - How to make two columns match dates relative to balance

    This will fix the dates if entered in column B so that they will be in the same format as in column J:

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


    I don't know what you mean by carry over and how exactly it will apply so I left it out. The yellow area has the totals for each system for each date.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Correlation - How to make two columns match dates relative to balance

    Right, I am not very good at explaining what I am after due to my limited understanding of excels functions.

    What you have done there in that spreadsheet makes it a little easier though.

    On each of the dates (chronological order) that you have within the yellow table, I want to know see the daily balance for system 1 and system 2 each and everyday, even if it remains unchanged on some days.

    So looking at what you have done there newdoverman, on the 06/03/2013:

    System 2 balance total was: £9,831.24
    System 1 balance total was: £10,689.60

    On 07/03/2013:

    System 2 balance total was: £9,831.24 (no was no Percentage Gain (change))
    System 1 balance total was: £10,816.60 (there was a Percentage Gain)

    and so on for every single day up until the end date of: 30/09/2014 (system 1 has a little too much data in there so ignore anything beyond that date <)

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Correlation - How to make two columns match dates relative to balance

    Here is the same worksheet with the last balance for each day for each system. The rest I will leave to you or someone else to figure out. I also made changes to the calculation involving C2 so that System 1 could be sorted in order and not lose the proper values.
    Attached Files Attached Files

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Correlation - How to make two columns match dates relative to balance

    The following two formulae should replace the formulae in columns K and L if you like. The columns function isn't necessary.

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

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

  8. #8
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Correlation - How to make two columns match dates relative to balance

    Thank you kindly newdoverman.

    I've cleaned up the dates using what you have mentioned.

    Could you kindly have a look at this spread-sheet. It should be very easy to understand what I am trying to do from here but I dont know the formula....

    I need to the Balance Daily Change for each and every date from 1st of April 2013 to 30th September 2014?

    Once I have this I can then do a correlation between both systems as I will copy the formula over and have all necessary data points within the date range
    Attached Files Attached Files
    Last edited by domgilberto; 10-22-2014 at 09:20 AM.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Correlation - How to make two columns match dates relative to balance

    This should fill in the blanks using a helper column. Note the formulae in columns F and G.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Correlation - How to make two columns match dates relative to balance

    Yes! Thank you so much!

    Here it is, thanks to your help I have managed to get both systems side by side with the correct days and correct daily balance change. Now... I want to find how positively or negatively correlated the "Balance" is on each system (just colour coded)

    What du reckon ?

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Correlation - How to make two columns match dates relative to balance

    Pearson correlation coefficient? http://en.wikipedia.org/wiki/Pearson...on_coefficient
    Easily calculated in Excel using either the CORREL() or PEARSON() functions: https://support.office.com/en-US/Art...rs=en-US&ad=US
    https://support.office.com/en-US/Art...rs=en-US&ad=US

  12. #12
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Correlation - How to make two columns match dates relative to balance

    Thanks MrShorty - yea I remember now... I knew it was something that straight forward.

    Thank you kindly for linking that in!

    With reference to the most recent spreadsheet I have attached, if I am getting a pearson or correl (both producing same results) of 0.04 on the "Daily Change (%)", I assume there is little to no correlation apparent meaning in the example spreadsheet I have attached, 4% of the time (within the sequence of data points under "Daily Change(%)" ) there is a positive coefficients (albeit barely anything!)

    Would I be correct in my observation?

    Thanks

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Correlation - How to make two columns match dates relative to balance

    I'm not a financial person but I think that your observation is correct as most of the values are around 0 within a small amount.

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Correlation - How to make two columns match dates relative to balance

    if I am getting a pearson or correl (both producing same results) of 0.04 on the "Daily Change (%)", I assume there is little to no correlation apparent meaning in the example spreadsheet I have attached, 4% of the time (within the sequence of data points under "Daily Change(%)" ) there is a positive coefficients (albeit barely anything!)
    I'm not a statistician, so I don't know. I'm pretty sure there is not a "4% correlation 96% uncorrelation" type of interpretation. As explained in the Wikipedia article, "The interpretation of a correlation coefficient depends on the context and purposes." The only universal interpretation that I know of is, again explained in the Wikipedia article, "1 is total positive correlation, 0 is no correlation, and −1 is total negative correlation." Anything in between those is going to depend on the context of the problem.

+ 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. Replies: 2
    Last Post: 06-25-2014, 10:08 AM
  2. [SOLVED] VBA Code to make two columns with dates the same
    By n_ant in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-18-2014, 01:23 PM
  3. Make two sets of columns match.
    By ragingfred in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2013, 03:10 PM
  4. Scatter chart/ correlation between 2 dates
    By ruangeld in forum Excel General
    Replies: 1
    Last Post: 07-07-2012, 10:02 AM
  5. Correlation formula between dates
    By Militis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-11-2010, 07:33 AM

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