+ Reply to Thread
Results 1 to 25 of 25

Calculating large amounts of =Correl

  1. #1
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Calculating large amounts of =Correl

    Hi Guys,

    I'm New to forum(but not to the "Umms"and"Errs" of Exel) I will start by saying good day to all.

    I'm quite new to Excel formulas and although I seem reasonably capable at using formulas on their own I am finding it difficult trying to link formulas together to create something simple and usable. After many day of head scratching I am offering this one up to the public in the hopes that someone can point me in the right direction for my research into solving my problems.

    I will attach the workbook in question and post as many clear images as I can to try and clearly identify the issues.

    Basics:
    I have 2 drop down menus which each contain 28 pair selection options.
    I need a formula that will identify which pair has been selected within each drop down box.
    Once identified, the formula will then select the corresponding column for that pair from the "Price History sheet" (all columns are named after pair)
    The formula then needs to calc the =CORREL between the two pairs selected and print a return value into a single cell.

    I have tried to attach a numerical value to each pair using sumif or vlookup 1-28 (28 pairs). Then using "IF(AND)" I tried to manually pair each numerical value for the pairs drop down to the appropriate correl formula.
    This did work, however I have 28x28 of these to input.
    This also prints a return value for each formula into its own separate cell and I need all the return values to be returned into a single cell.

    The third drop down menu "Time frame":
    This formula needs to identify the selection from the "time frame" dropdown.
    Dependent on the time frame chosen, it then needs to select the appropriate length of time/row-columns from the Price history sheet and apply the =CORREL to the selected pairs specific to the time frame chosen.
    This formula would need to be dynamic in some sense.The Price History Sheet will continue gathering data and grow in size. The time frame formula needs to move down as the data is added. (e.g. It needs to select the most recent weeks,months,year of date and not just keep displaying the same range)

    Closing Price:
    This sheets brings in prices and updates live.
    I am lost on this one.
    Is it possible to have the live closing prices for each pair print onto the Price History sheets on a per week basis?

    Ideally the formula would take the closing prices from the chart and print a new horizontal row within the price history sheet on a set date weekly. (e.g. Every sunday at 12pm the formula prints the current live price data into horizontal row of price history sheet.
    This would need to print a new week ending date in column A and then apply the right closing price to the corresponding column.

    Wow! I thought I just had a couple of small issues to resolve:D

    I hope I explained things in an understandable format but please forgive me if this is not the case. If anyone has any questions then please feel free to ask.
    I am not looking for someone to do my homework for me or anything, but if anyone could advise me as to whether these issues can be resolved and guidance towards direction or the types of logic needed to progress would be greatly appreciated.

    Thank you for your time.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    the description of your problem is rather wordy so let me know if i have digested it correctly

    so lets break it down to its components
    1>
    you want to match the selected price history of Currency pair/Correlation pair using the Correl Formula

    2>
    you want to somehow limit the time frame of how far back the price history actually looks

    3>
    gather the closing price for the selected pairs on the closing price sheet?


    does that about sum it up in a nut shell?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    i have based this file on the above assumptions

    hope i have put enough details in explaining my formulas in the worksheets
    essentially i am using OFFSET to manage the range of which CORREL looks at

    brief basic explanation of offset
    http://www.excel-easy.com/examples/offset.html
    im sure you can find more if you require just google it

    re: closing price i just made a helper column to put everything into two columns
    then used a vlookup
    if this cannot work for you i will think of something more convoluted but this should work fine?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    Hi Humdingaling,

    Thanks for taking the time to reply.

    Its nice to know that despite a terrible title and a lengthy post that there is someone willing to try and make sense of it all :D
    My apologies for the rather "wordy" problem. I was worried that if I tried to put down a brief description of the issue that it would create more questions than answers.

    In a nutshell.....By Jove he's got it!

    Great summoning up there.

  5. #5
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    I am stuck for time atm but having looked briefly at the details you have added to the workbook I think you may truly pointed me in the right direction. I had already spent some time trying to use the offset and index formulas to resolve some of my issues. Its good to know that I may have vaguely had the right idea, but the lack of knowledge(as always) held me back somewhat.Once I have been through the workbook and reverse engineered some of your forumlas, I will come back with the completed workbook and mark the thread as solved.

    I would like to thank you for your time and effort in not only answering the thread but also providing me with the detail and explanations that you have within the workbook. These were clear and should now be easy to interpret.
    At this time I am not 100% sure about the Part 3 closing price data using vlookup. Although the helper column and vlookup option are working well, this may only have solved half of what this formula needs to complete.
    However you have hopefully provided me with enough food for thought and rather than bang on about things I will take on all your help and comments and hopefully use that to complete the task.

    I hope you don't mind me updating the post with any current progress or issues along the way.

    Thanks again.

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    not a problem

    wordy descriptions are better than the "mind reading" problems
    so i can appreciate the time and effort you put in to explaining your problem

    Re:Part 3
    i didnt really understand the whole problem to be honest so i put what i thought was the problems but if it is not please elaborate

    i generally remain subscribed to the threads i answer for a very long time

    take your time deciphering
    ill still be around =)

  7. #7
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    Hi,

    Thanks for adding the formulas and descriptions to the workbook. I have had a good look at things and I am really impressed with the ease and skill in which you did that. The use of simple logic,added to the right part of the formula amazes me.
    At that point it became obvious that I prob would not have been able to accomplish this task solely.

    I would like to put forward a few questions relating to some of the formulas to help me get a better understanding of whats gone on.

    Part1
    Would I be right in thinking that the pair1/pair2 named ranges and formula serve no real purpose in the overall calculation?
    Has this formula has been mapped out by you separately to give me a better understanding of how this links in with timeframe formula. The true final pair3/pair4 correl calc consists of this formula combined with the timeframe formula and therefore gives the correct final correl calc?

    Pair1 =OFFSET('Price History'!$A$2,0,MATCH($L$3,'Price History'!$B$2:$AC$2,0),COUNTA('Price History'!$A:$A)-2)
    Pair2 =OFFSET('Price History'!$A$2,1,MATCH($L$3,'Price History'!$B$2:$AC$2,0),COUNTA('Price History'!$A:$A)-2)

    This is the only formula that i am having some trouble with.
    Would I be correct in thinking that:
    offset is locating the data.
    Match is matching the drop down box with the named rows in price history
    counta is applying the range by counting the rows(64-2) is the -2 to eliminate the header rows?

    What i don't seem to understand is how this formula is returning the value that it is?
    =OFFSET('Price History'!$A$2,0,MATCH($L$3,'Price History'!$B$2:$AC$2,0),COUNTA('Price History'!$A:$A)-2)
    =OFFSET('Price History'!$A$2,1,MATCH($M$3,'Price History'!$B$2:$AC$2,0),COUNTA('Price History'!$A:$A)-2)
    One of the small differences here is the row no- in the offset, yet this number makes a big difference to the final correl result.It seems that if this is not set to 0&1 across the two pairs formula it will print a wrong value.
    Could you please explain this?

    Part2:

    These simple formulas worked great and I will be using these across many different things. I can't tell you how many different ways I tried achieve the same result, so I had had to smile when I saw those working

    Part3:
    I will try to better expain myself regarding this.

    The platform that i am using to retrieve the price history data does not seem to have a method for importing history automatically into excel(very frustrating)
    Therefore in order to keep things up to date I would have to manually copy and paste new data into the price history.
    The current price history sheet is made up of weekly closing price data only and therefore would need updating weekly
    This alone would possibly be manageable,However If I were using price history from an hourly time frame, then this data would need updating on a hourly basis and hopefully you can now see that is would be too much data to keep adding manually

    The fix?
    Having given this some thought I thought it may be possible to bring in the live currency price data. This data updates live and by the second within excel but only when my third party platform is open.
    I was wondering if was possible to use this data to then print update values within the price history sheet at given time intervals eg. every hour, 7 days,etc? Thus udpdating and adding to the price history data.

    e.g pseudo formula based on weekly price data

    Check time and date,IF time=10pm-Sunday, Print current live closing prices into correct columns within price history sheet.

    Based on an hourly it could be:
    Check time and date,IF 1 hour has passed, Print current live closing prices into correct columns within price history sheet.



    The problem I have here is accuracy. If I cannot find a way to print the current live price in the price history sheet at a specific time chosen by me, then the data will not be accurate and void.

    I did try to keep this one brief and to the point :D

    I hope my questions are easy enough to understand and forgive me for my lack of knowledge.Although your formulas work, if I cannot fully understand how something is working then i will not be able to apply it to a given situation.
    I apologize for going into some detail about the nature of the workbook but I hope the small insight makes the thinking easier.

    Thanks,

  8. #8
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    Hey,

    Ive attached a quick mock up offset test to hopefully easier describe my lack of understanding about the formula.

    If it makes things worse the just delete it :D

    Thanks.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    Would I be right in thinking that the pair1/pair2 named ranges and formula serve no real purpose in the overall calculation?
    yes named ranges make it easier to type in formula
    though the real reason i use it in named range ...it makes it easier to check if the range that is created from the offset is actually right which formula doesnt do

    some reading for you
    http://www.contextures.com/xlNames01.html
    "Dynamic Named Range - Formula" is where you want to read from
    believe there is a little video that accompanies it

    offset is locating the data.
    Match is matching the drop down box with the named rows in price history
    counta is applying the range by counting the rows(64-2) is the -2 to eliminate the header rows?
    Counta doesnt work that way
    COUNTA(A:A) counts all the cells in column A that isnt empty

    so in your original file you had
    row1 = Close Prices
    row2 = DATE (with all the headers)
    this is why i have minus 2
    if i had made the formula COUNTA(B:B) i would only need minus 1 as cell(B1) doesnt have anything in it

    a little more practice on part 1 and i think part 2 shouldnt be too much further to understand
    i had to mess around a little bit with the help of named range to see if formula is picking up the correct range

    let me digest part 3 and return file in next reply

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    attached is your example with some pictures to help you along in understanding more some of the concepts used
    named range
    Offset
    match
    Counta


    Part 3
    Therefore in order to keep things up to date I would have to manually copy and paste new data into the price history
    does the format change or does the formula get over written?
    in the helper column i posted i put link to the cell is a formula not the actual number eg GBP/AUD = =I12

    However If I were using price history from an hourly time frame, then this data would need updating on a hourly basis and hopefully you can now see that is would be too much data to keep adding manually
    If I cannot find a way to print the current live price in the price history sheet at a specific time chosen by me, then the data will not be accurate and void.
    so basically if i understand you correctly
    you open up your 3rd party software
    it update constantly but you want it to "snapshot" prices every hour

    i can certainly advise you of some sort of "internet" solution on how this can possibly be done but
    generally this would call for some IT intervention if it is business critical (which is sounds like it is)

    Problem with any Time formulas in excel
    it doesn't REFRESH automatically
    so your pseudo formula fix doesnt really work...as such you probably need to look into VBA/Macro solution

    while i can certainly help provide solution for this...not sure how your VBA knowledge is and/or how much of a crash course you are willing to take (not withstanding you would want to know exactly how the code works and how to fix it should it be required)
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    Hey,
    Thanks for your explanations and input. I am feeling enlightened! I can see where I have making errors and those last couple of posts have filled in the gaps I had there between offset match and count. The use of the named ranges to confirm the selection was great way to get a visual on things. I just need to break things down a bit more and tackle each task separately first and make sure any counting has correct +- values in relation to the offset range and direction i want to be counting in.

    does the format change or does the formula get over written?
    in the helper column i posted i put link to the cell is a formula not the actual number eg GBP/AUD = =I12
    Sorry but I not sure what you mean here? I think you may have answered this when you wrote?
    so basically if i understand you correctly
    you open up your 3rd party software
    it update constantly but you want it to "snapshot" prices every hour
    This is the correct understanding!
    The closing price data is not a set value and will change its values every second when third party software is running
    The problem with the helper column and vlookup is that it needs to take a 'snapshots' of the changing price data. at pre determined time intervals and then print the result in the correct date/row---currency pair/column of the price history tab.
    The idea of If(date and time=My predetermined date/time values),take 'snapshot' of closing price and print value in correct sheet/row/column
    seemed like a perfect plan but I understand what your saying about the way the date/time works within excel.

    I do believe I have heard the solution may be possible with VBA. Overall the tool is now looking quite powerful and I am really pleased with the efforts and results made thus far. I will be using it on a daily basis and a long as I find a method for sensibly updating 3/4/ price history sheets it will be useful over a lifetime. This aspect is critical to the overall success as without it I will be very limited in using it to its full potential.

    Therefore it seems that if I have take my knowledge and understanding further then it must be done

    I'll be brief here:
    Actual VBA knowledge = 0
    Willingness= 100%

    I did some mel scripting for autodesk 3d software in university some I do have some basic scripting knowledge but this may not transfer over to VBA.
    As you stated, I would need to fully understand what the code is doing in order to modify it but with enough ////comments, it may be possible :D

    I suppose the best idea would be to to try and advise me on where to head next

    I have attached an update of the current book. Its getting a bit messy in there now but hopefully you can get an idea of how the overall book and different sheets will look. and work when finished.
    Although things in there seem to make sense after reading todays posts I will make sure I go back through the ranges and do some testing to validate the datas accuracy as this thises important steps have not been take yet.
    I think most of the questions and issues I had in there have been answered here and I should be able now correct them.

    Thanks again.
    Attached Files Attached Files
    Last edited by gm90; 03-20-2017 at 10:43 AM.

  12. #12
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    Counta doesnt work that way
    COUNTA(A:A) counts all the cells in column A that isnt empty

    so in your original file you had
    row1 = Close Prices
    row2 = DATE (with all the headers)
    this is why i have minus 2
    if i had made the formula COUNTA(B:B) i would only need minus 1 as cell(B1) doesnt have anything in it
    Please correct me if I'm wrong here:
    The counta can either have a count from the bottom up or count from the top down depending on whether we add or subtract the vlookup (also by include/exclude rows with a + or - value within the counta). As we wanted to calcl the correl from the bottom up we do not need to subtract the headers with a -2. This would result in time period being the same the range but the correl calcs start date would be 2 rows higher than needed.

  13. #13
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    I may have found something that may be usable for deconstruction purposes or even to draw data from into the history charts.
    Should we take this somewhere else? I'ts getting a little off topic now and the initial query of the thread was answered.

    Thanks for the input.
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    Please correct me if I'm wrong here:
    The counta can either have a count from the bottom up or count from the top down depending on whether we add or subtract the vlookup (also by include/exclude rows with a + or - value within the counta). As we wanted to calcl the correl from the bottom up we do not need to subtract the headers with a -2. This would result in time period being the same the range but the correl calcs start date would be 2 rows higher than needed.
    CountA just counts everything that is not blank
    it does so regardless of bottom or top
    so if you have a blank cell in the middle it will skew your number

    this is not the solution i use for all the cases
    sometimes i use a combination of CountIF, countblank,CountA
    it depends what is easiest to code and applies to the data presented

    the end game whatever you use is to give you number of rows you want to put into your array

    had a quick look at your File Correlation test v2
    it seems you are still putting in 1 for offset of rows
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    the red coloured should be zero
    as you dont want to offset down 1 row from A2

    that seems to be the main issue
    the other one is trying to make correl compare 1 data point vs 1 data point
    it just spits out div/0
    Last edited by humdingaling; 03-20-2017 at 08:28 PM.

  15. #15
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    I hear what your saying. I think the initial issue was my lack of understanding of the formulas and as I stated yesterday you really cleared those up with the extra post yesterday.
    the other one is trying to make correl compare 1 data point vs 1 data point
    it just spits out div/0
    Tbh I thought that my divo errors were natural errors? These occur when the drop down menus are selected to an option which cannot compute. eg cannot use a weekly time period to get monthly correl(monthly needs to be set to 1 month min.)

    I may but wrong but when i have tried any basic =correl it needs to have at least 2 rows in each array to compute. Even if I use=correl on my lists in its most basic form it always gives me a divo if i try set both arrays to only a single cell. Is this not true?

    All the vlookup charts created for the range selection eg Day=1row, 1 week=2rows, 1 month=4rows all use 2 as the min value for correl. Anything below 2 should give a divo because your asking it to correl too small a timeframe for the chosen chart. (you cant correl 1 day of data in a weekly chart that only prints a close price once every 7 days)

    Having ran some manual correl checks on my history lists over different lists and time frame to match my drop downs, it seems to me like the drop downs are computing the right results.

    My intial problems with counta was from looking at your formulas which you first wrote, The comments read: =OFFSET('Price History'!$A$2,0,MATCH($L$3,'Price History'!$B$2:$AC$2,0),COUNTA('Price History'!$A:$A)-2) but the actual
    named ranges for same pairs read: =OFFSET('Price History'!$A$2,1,MATCH($L$3,'Price History'!$B$2:$AC$2,0),COUNTA('Price History'!$A:$A)-2)

    Having read you extra comments though yesterday about named ranges, offset,match and count I can see the error of my ways now

    Does your knowledge of excel come through your working background?
    Last edited by gm90; 03-21-2017 at 07:23 AM.

  16. #16
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    I may but wrong but when i have tried any basic =correl it needs to have at least 2 rows in each array to compute.
    this is my understanding of the formula
    i haven't really used this formula before to be honest

    Re:Knowledge
    the basics was during work
    the extras came after learning one formula at a time
    once you have a critical mass of understanding (doesnt look to me you are that far off)

    its about practice and continual learning which online forums has been wonderful
    also doesn't hurt being proficient in web searching (i actually did a university subject on the matter....before google existed mind you)

    keeping a log of previous examples of solutions found/created/adapted helps the old ticker

    Re: VBA
    probably a good place to start is google
    here are some i found which dont look too bad
    http://www.excel-vba.com/excel-vba-s...-beginners.htm
    http://www.homeandlearn.org/add-the-...-to-excel.html
    if you're more a video person there are plenty of youtube

    the example you posted doesnt really do what you want
    majority of the code is to fetch data off Oanda - you dont want/need anything like that because your MT does that already

    i have attached back in file very rudimentary code to start things off
    Please Login or Register  to view this content.
    it works because of how i setup closing price sheet (which may or may not work)
    i dont know how MT updates your file
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    Hey.

    Thanks again for your time and explanations Hum.

    I think thats why Dads my hero. He used to fix things and make them work in world where no information was readily available or easily accessible
    Thats a good point you make about keeping a log. I could easily see how you could lost in an old formula that you haven't used in sometime. As I have taken the time to understand these formula rather than copying and pasting, I will def keep these logged now. I can see many different uses for the things we've discussed.

    Back to case and point:

    Thanks for the VBA links. I had already bookmarked excel VBA as a potential starting point for VBA so I'm glad you've put that one up.
    the example you posted doesnt really do what you want
    majority of the code is to fetch data off Oanda - you dont want/need anything like that because your MT does that already
    I think this is a major issue of my project and in retrospect I prob should have worked backwards on this one.

    I think the main problem is actually that my MT does NOT do this already.
    The Price History lists that I have generated this far have all been created manually. This involved taking all the data(28 pairs) individually from my MT and compiling it in one workbook for each of the chart time periods( huge amounts of work)
    My MT has a dde option which is why I can get the live closing prices within excel but that's all.

    Does the basic VBA script that you have created only work when both my MT platform and excel are open at the appropriate times and dates?
    This may leave me in a position where I can only gather data if my applications are running 24/7?

    Although this would work I would not be the best solution.
    In an ideal world I would be able to open excel at any given time and the relevant data missing from the time period of MT and excel being closed would then update.
    This is why I thought it may be a better idea to try and find a method of drawing in Price history data from a website.

    This could either check excel for existing price history and dates.Then update the rows from the last matching date between excel and the data source.
    Possibly a more simple method would be to just have excel overwrite all existing data and add new data also.

    Although each broker will have slight variation in closing prices due to there own fees and charges, I think due to to large amount of data that's needed in the database It would be better to have a robust importing and updating system (even if this draws data from a source outside of my usual broker)

    Am I just a major pain in rear?

    Thanks for the VBA Hum. I'll look at this over the coming days and see where it take me.

    I really appreciate your efforts thus far and I could not have taken things this far without your knowledge and advice. I may be forced to buy a round in If you ever find yourself in the UK

  18. #18
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    there in is the underlying issue
    i have no idea what MT does and what it can do. i can only give advice on what i can see in example and make assumptions
    i start making too many assumptions then the outcome veers far away from the original intent

    Does the basic VBA script that you have created only work when both my MT platform and excel are open at the appropriate times and dates?
    This may leave me in a position where I can only gather data if my applications are running 24/7?
    i would assume so, i only get "REF" errors because i dont have MT
    in order for any code in vba to run, excel needs to be open...it doesnt run in the background

    In an ideal world I would be able to open excel at any given time and the relevant data missing from the time period of MT and excel being closed would then update.
    This is why I thought it may be a better idea to try and find a method of drawing in Price history data from a website.
    suppose that would be the ideal
    i think its going to a grey area of i dont really know what you do for work and how you do it to tailor an outcome for you
    not really what i do....can only provide advice on the excel question

    This could either check excel for existing price history and dates.Then update the rows from the last matching date between excel and the data source.
    Possibly a more simple method would be to just have excel overwrite all existing data and add new data also.
    the logic to define what is missing is much harder to define as you rightly point out
    the problem with clean slate wiping is the HUGE amount of data involved (unless you are planning to do some sort of rolling update..whereby old data falls off)

    yes you are right data base solution seems like where it will need to head to...unfortunately not my area of expertise
    though this sounds like wheel building exercise, surely there is something out there that does this already?
    would of though the value in FX trading is the comparative work not the data itself (i may just be dreaming )

  19. #19
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    I have to admit that the rabbit hole became much deeper than I had anticipated.
    I terms of a wheel building exercise in some respects this is true.
    There are various pair correl calcs available online as well as various demo and tutorials on creating them within excel.
    All the ones that I have encountered fail to give you some of the options necessary to creating accurate data(maybe these sites just want to take your money )

    The decision to create something myself was an easy one and I thought excel would be a good choice for this.
    I already use excel for personal logs and data collection and I can think of many different uses for the large amounts of history data collected. It seemed like a good opportunity to expand on my knowledge and use of excel.
    The interface has now been checked and is fully working. I have ran manual tests and checked all named ranges to ensure they are selecting the right data.
    I have say, this little tool is awesome! Its already shaping up and giving more accurate results than anything online. My problem with excel is that I always feel that I am one small formula away from completing my task.

    In terms of actual trading..This tool is only a tiny part of it.Without getting too dull,Pair correlation is crucial for trading multiple currency's and shouldnt be overlooked.
    I was 'simply' doing my take on it.

    MT also has its own scripting language.
    Similar tools have also been created within MT but most of these are built to the programmers spec.
    I think its common in all walks of life to see or use things and think "wouldn't this just be a little bit better if.."

    I do feel a solution lies within excel which is why I brought my issues up here.
    The secret for me lies within the amount of information I could draw from a solid database with excel
    I may have to look into scripting within MT but as I explained for the reasons above, excel was my first choice.

    It would be nice to create a history that could build up over years but a rolling update could work just fine. Could this handle maybe 1500 rows X 4 sheets? (My largest sheet is currently 320 rows)

    I will take some time to look at your VBA, do some reading and look at the other oanda excel doc.
    The tool is complete...just this data import issue.

    I feel like the oanda excel doc is kinda of doing what i need. Its just not doing it in a way that suits my needs.
    As a scripts it lets you choose your set dates and currency pair.
    Then it brings the price history data in from its website and adds it to a new sheet
    it then uses that data to create a graph.

    It doesn't seem too far away and I only need two out of those three things this make me believe that excel may be fit for purpose here.
    If this info is free to source ,I am still convinced I may be able to extract elements of this script and use it to generate the history lists.

  20. #20
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    I think its common in all walks of life to see or use things and think "wouldn't this just be a little bit better if.."
    its also how improvements come about which is why i think there is a mt5 out now

    Could this handle maybe 1500 rows X 4 sheets? (My largest sheet is currently 320 rows)
    i work with spreadsheets that go up to the 400000+ lines
    hella slow and probably poorly designed (not my work i just have to use it)
    so yes..well within scope

    The tool is complete...just this data import issue.
    fundamentally this is actually the most important part
    if you find a way to scrap the data and its in an nonconstructive format, then you really don't have anything at all

  21. #21
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    Haha, This true

    Yes mt5 exists but not all brokers offer this. Most even offer there own web based platforms.
    This only further highlights my case for using excel.

    Once finished, this tool could potentially work independent of any platform. I do not need to worry about changing brokers or platforms and at least may just need to change the import directories. MT4 is old now but I guess one of the reasons people stick with it is the amount of tools they have for it which would be unavailable or have to be completely rewritten to suit a new platforms. Even mt5 uses a newer language.
    Even if I look at other tools available, without a working knowledge of the scripts I have no real way of verifying the data and most only cover small pair selections due of amount of data maybe and have limited options.
    I'm hoping to avoid such things with this tool. If this tool had to continue to draw in data for mt4 then thats how would it would have to work but prob an external source would be even better.


    Damn! That's some big spreadsheets. Its awful when you have to work with something that's poorly designed Good to know that it could easily handle the small amounts of data overwriting required by me at least.
    I think it wasn't until sometime in that i realized how much trouble the importing was going to cause me.

    Would you like turn to more simple times?
    I was already working on expanding the sheets and formulas and even without a solid method for updating history I can still make use of the tool but in a more limited way.
    Its been good getting more into excel and you've taken my understanding to another level. I've been trying to expand on this and create some new features.
    I was working on a new sheet to create a table that works alongside the dropdown menus using my new found knowledge gained from you
    And as always my logic is wrong!

    Would you please be able to have a quick look at this?

    I have commented on the sheet and I am sure I know what the issue is but have not been able to resolve it.
    It seems like this formula is just missing something small and simple,overlooked by me.
    The speed from your original post tells me that what may day me days to resolve could be just minutes for you

    Thanks again Hum. Its been a great conversation so far and Ive enjoyed the learning process.
    I don't feel its been a wheel building exercise as the tool is giving data beyond what i can find using online tools.
    Regardless of the outcome though its been a great excel exercise for me.
    Attached Files Attached Files
    Last edited by gm90; 03-23-2017 at 09:08 AM.

  22. #22
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    i only regigged your xpairday and ypairday
    it takes some fiddling around to get it work properly

    xpairday i have made
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    breaking down into the components
    starting point again is 'Daily Price History'!$A$2

    HEIGHT = VLOOKUP('Pair Selection '!$N$3,'Pair Selection '!$I$3:$J$8,2,0)
    which is your time frame correlation DV, in said example is 300

    Rows offset
    = COUNTA('Daily Price History'!$A:$A)-1-VLOOKUP('Pair Selection '!$N$3,'Pair Selection '!$I$3:$J$8,2,0)
    this is the same as previous formulas based on the total rows - 1 - height = 20

    here is today's lesson for the day
    https://exceljet.net/named-ranges
    Look under relative named references

    Columns uses the formula MATCH('Correlation Chart'!B$4,'Daily Price History'!$B$2:$AC$2,0)
    but notice that there is no $ in front of B
    meaning it will move as the cell that uses the named range moves

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


    ypriceday is exactly the same except for the COLUMN in offset which is now
    MATCH('Correlation Chart'!$A5,'Daily Price History'!$B$2:$AC$2,0)

    ps
    maybe suggest you rijig your spreadsheet to use indirect to reference the worksheets that is to be used
    here is a very basic example of it in action
    http://spreadsheetpro.net/how-to-mak...-spreadsheets/
    so rename your sheets to DAILY, WEEKLY,MONTHLY and have a DV that says exactly this

    then will take some serious mapping to apply and change in your example but i believe it will be rewarding and challenging
    however something that is really time consuming and since it really isnt my own project i wont be doing for you
    Attached Files Attached Files
    Last edited by humdingaling; 03-23-2017 at 09:50 PM.

  23. #23
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    very basic example of indirect DV i mentioned in previous post but should get your ticker going with ideas
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    03-15-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    13

    Re: Calculating large amounts of =Correl

    Absolute or relative named ranges...Fantastic!! I can already see whats going there.
    I can also see how the use of indirect could have saved a lot of time and was definitely what I should have been of been using to begin with.
    As i mentioned in an early post I dont do too bad with individual formulas, its the nesting of these formula that always beats me
    Thanks again for the sound explanations and examples Hum.

    I need to now take all this in and apply it. And also continue my quest for a sensible way of drawing in data.
    Ive had a busy week so time has been limited to carry on with the VBA you sent me. Between that and the extra explanations today, have a lot to work through here now.
    Should the vba update the date in row A and then print the prices?
    Currently it will update the date but only if run it. All the columns print but show N/A
    It seems that the script thus far is:
    finding the right row.
    Copying the ranges from closing price to price history.
    Updating the row with current date and print ranges (currently N/A error)
    What seems to be missing is the option to create the 'snapshot' of the ws1 ranges at a pre determined time.

    Or should the idea be that i run the script at whatever time suits me and that then creates the snapshot?
    Its hard to see which way it works atm due to it giving me NA rather than a price.


    I dont want to bother you too much with questions about the vba atm as I have not had time to properly look into things properly.
    It does look like a good starting point though and something which could definitely be used even if only over longer chart time periods.

  25. #25
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculating large amounts of =Correl

    Should the vba update the date in row A and then print the prices?
    dont know what you mean by this but if the shoe fits

    Updating the row with current date and print ranges (currently N/A error)
    this is probably more due to my not understanding how MT works

    there is a thing called break point in VBA
    which lets you go thru the code step by step
    helping you more break down the code more
    http://www.excel-easy.com/vba/examples/debugging.html

    anyways i think you have more than enough on your place currently redesigning your spreadsheet with optimal formulas

+ 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] HELP - This Macro works on small amounts of data but fails on large amounts
    By BookmanNLA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-18-2015, 12:40 AM
  2. Using large amounts of IF statements
    By Malteser in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-06-2015, 03:18 PM
  3. Large amounts of data
    By namluke in forum Excel General
    Replies: 3
    Last Post: 12-11-2014, 02:40 PM
  4. Replies: 1
    Last Post: 05-07-2014, 06:19 PM
  5. [SOLVED] Calculating amounts in a single currency from a list of multiple currency amounts
    By Romsky in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-03-2013, 12:22 PM
  6. Large Amounts of Data
    By Drewser33 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-17-2008, 09:13 AM
  7. Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(.
    By Emmanuel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-12-2005, 11:40 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