It looks like you're new here. If you want to get involved, click one of these buttons!

- 141.5K All Categories
- 104.7K Programming Languages
- 6.4K Assembler Developer
- 1.9K Basic
- 39.8K C and C++
- 4.3K C#
- 7.9K Delphi and Kylix
- 4 Haskell
- 9.6K Java
- 4.1K Pascal
- 1.3K Perl
- 2K PHP
- 518 Python
- 37 Ruby
- 4.3K VB.NET
- 1.6K VBA
- 20.8K Visual Basic
- 2.6K Game programming
- 312 Console programming
- 89 DirectX Game dev
- 1 Minecraft
- 110 Newbie Game Programmers
- 2 Oculus Rift
- 8.9K Applications
- 1.8K Computer Graphics
- 730 Computer Hardware
- 3.4K Database & SQL
- 522 Electronics development
- 1.6K Matlab
- 628 Sound & Music
- 257 XML Development
- 3.3K Classifieds
- 196 Co-operative Projects
- 185 For sale
- 189 FreeLance Software City
- 1.9K Jobs Available
- 600 Jobs Wanted
- 201 Wanted
- 2.9K Microsoft .NET
- 1.7K ASP.NET
- 1.1K .NET General
- 3.3K Miscellaneous
- 4 Join the Team
- 0 User Profiles
- 353 Comments on this site
- 59 Computer Emulators
- 2.1K General programming
- 182 New programming languages
- 604 Off topic board
- 170 Mobile & Wireless
- 44 Android
- 124 Palm Pilot
- 335 Multimedia
- 151 Demo programming
- 184 MP3 programming
- 0 Bash scripts
- 19 Cloud Computing
- 53 FreeBSD
- 1.7K LINUX programming
- 367 MS-DOS
- 0 Shell scripting
- 320 Windows CE & Pocket PC
- 4.1K Windows programming
- 895 Software Development
- 408 Algorithms
- 68 Object Orientation
- 89 Project Management
- 90 Quality & Testing
- 239 Security
- 7.6K WEB-Development
- 1.8K Active Server Pages
- 61 AJAX
- 2 Bootstrap Themes
- 55 CGI Development
- 19 ColdFusion
- 222 Flash development
- 1.4K HTML & WEB-Design
- 1.4K Internet Development
- 2.2K JavaScript
- 34 JQuery
- 284 WEB Servers
- 151 WEB-Services / SOAP

Lucasinlondon
Posts: **1**Member

in VBA

Hi there,

First of all apologies for the long post. Hope someone can offer some advice.

I have about 200 columns of time series data that I need to perform a correlation analysis on in terms calculating lagged cross correlations between all the variables.

Currently the data is stored in Excel. Variable 1 is in Column A, variable 2 in Column B etc, the data ends in column GR which contains the last variable. I need to calculate the cross correlations between variable 1 in Column A and all other variables in the other remaining columns. The key thing here is that I need to calculate lagged as well as contemperaous correlations between each of the variables.

Then I want to do the same for the next variable in Column B, i.e. calculate contemperaous and lagged correlations between variable 2 and all other variables.

Is there anyway to automate this so that it work out the correlations up to say 10 lags of for each variable against all other variables and paste the results in some kind of readable grid in an excel sheet for each variable:

For example, for Column A - Variable 1 the results could be shown like this (for the first five variables):

B C D E F

Lag0 X X X X X

Lag1 X X X X X

Lag2 X X X X X

Lag3 X X X X X

Lag4 X X X X X

Lag5 X X X X X

Lag6 X X X X X

Lag7 X X X X X

Lag8 X X X X X

Lag9 X X X X X

Lag10 X X X X X

Where X represents the correlation coefficient between variable 1 and all the other variables (col B, C, D, E, F to Col GR) at different lags. The output doesn't have to be exactly like this though.

I can do this manually in excel using the Correl function, adjusting the series range in the formula every time I want to work out the lagged correlation.

Background: The correlation the function takes two arguments:

Array1, Array2

So to calculate correlations for Column A against all other columns I would input:

correl (A2:A100, B2:B100) to give the contemperous correlation between Col A and Col B, correl (A2:A100, c2:c100) to give correlation for col C vs. Col A etc.

Then to work out the correlation at 1 Lag I change the formula to (A3:A100, B2:B99), at lag 2 the formula becomes (A4:A100, B2:B98) etc. Then I do this for all the other columns/Lags but to do this for 200 variables, would take a huge amount of time (probably several days)! Is there a way of doing it using VBA perhaps? Even if it takes a few hours that's OK, as I can leave it to run in the background.

The way I'm thinking of doing this is setting up a workbook with the following sheets:

1) Source Sheet - this would contain all the raw data (Columns 1-200)as described in the top of the post.

2) Calculation sheet - Here I would have a duplicate of the above sheet but with one extra column (say column A) and so the dataset would end in column 201). Beneath this matrix I would set up formulas using the Excel

First of all apologies for the long post. Hope someone can offer some advice.

I have about 200 columns of time series data that I need to perform a correlation analysis on in terms calculating lagged cross correlations between all the variables.

Currently the data is stored in Excel. Variable 1 is in Column A, variable 2 in Column B etc, the data ends in column GR which contains the last variable. I need to calculate the cross correlations between variable 1 in Column A and all other variables in the other remaining columns. The key thing here is that I need to calculate lagged as well as contemperaous correlations between each of the variables.

Then I want to do the same for the next variable in Column B, i.e. calculate contemperaous and lagged correlations between variable 2 and all other variables.

Is there anyway to automate this so that it work out the correlations up to say 10 lags of for each variable against all other variables and paste the results in some kind of readable grid in an excel sheet for each variable:

For example, for Column A - Variable 1 the results could be shown like this (for the first five variables):

B C D E F

Lag0 X X X X X

Lag1 X X X X X

Lag2 X X X X X

Lag3 X X X X X

Lag4 X X X X X

Lag5 X X X X X

Lag6 X X X X X

Lag7 X X X X X

Lag8 X X X X X

Lag9 X X X X X

Lag10 X X X X X

Where X represents the correlation coefficient between variable 1 and all the other variables (col B, C, D, E, F to Col GR) at different lags. The output doesn't have to be exactly like this though.

I can do this manually in excel using the Correl function, adjusting the series range in the formula every time I want to work out the lagged correlation.

Background: The correlation the function takes two arguments:

Array1, Array2

So to calculate correlations for Column A against all other columns I would input:

correl (A2:A100, B2:B100) to give the contemperous correlation between Col A and Col B, correl (A2:A100, c2:c100) to give correlation for col C vs. Col A etc.

Then to work out the correlation at 1 Lag I change the formula to (A3:A100, B2:B99), at lag 2 the formula becomes (A4:A100, B2:B98) etc. Then I do this for all the other columns/Lags but to do this for 200 variables, would take a huge amount of time (probably several days)! Is there a way of doing it using VBA perhaps? Even if it takes a few hours that's OK, as I can leave it to run in the background.

The way I'm thinking of doing this is setting up a workbook with the following sheets:

1) Source Sheet - this would contain all the raw data (Columns 1-200)as described in the top of the post.

2) Calculation sheet - Here I would have a duplicate of the above sheet but with one extra column (say column A) and so the dataset would end in column 201). Beneath this matrix I would set up formulas using the Excel

Terms of use / Privacy statement / Publisher: Lars Hagelin

Programmers Heaven articles / Programmers Heaven files / Programmers Heaven uploaded content / Programmers Heaven C Sharp ebook / Operated by CommunityHeaven LLC

© 1997-2015 Programmersheaven.com - All rights reserved.