Regression Analysis with macro's

A

AdiVohra

New Member
Joined
Aug 31, 2014
Messages
24
I have a table as such with lots of different data values for different currency pairs
GBP/USD
EUR/USD
USD/JPY
USD/CAD
DATE
RATE
DATE
RATE
DATE
RATE
DATE
RATE
01/07/2014
1.6475
01/07/2014
1.3178
01/07/2014
103.67
01/07/2014
1.092
02/07/2014
1.6409
02/07/2014
1.346
02/07/2014
103.8
02/07/2014
1.079
03/07/2014
1.6578
03/07/2014
1.344
03/07/2014
103.93
03/07/2014
1.0789
04/07/2014
1.658
04/07/2014
1.362133
04/07/2014
104.06
04/07/2014
1.0702
05/07/2014
1.309
05/07/2014
1.375233
05/07/2014
104.19
05/07/2014
1.06365
06/07/2014
1.66
06/07/2014
1.388333
06/07/2014
104.32
06/07/2014
1.0571
07/07/2014
1.502233
07/07/2014
1.401433
07/07/2014
104.45
07/07/2014
1.05055
08/07/2014
1.475576
08/07/2014
1.414533
08/07/2014
104.58
08/07/2014
1.044
09/07/2014
1.448919
09/07/2014
1.427633
09/07/2014
104.71
09/07/2014
1.03745
10/07/2014
1.422262
10/07/2014
1.440733
10/07/2014
104.84
10/07/2014
1.0309
11/07/2014
1.395605
11/07/2014
1.453833
11/07/2014
104.97
11/07/2014
1.02435
12/07/2014
1.368948
12/07/2014
1.466933
12/07/2014
105.1
12/07/2014
1.0178
13/07/2014
1.34229
13/07/2014
1.480033
13/07/2014
105.23
13/07/2014
1.01125
14/07/2014
1.315633
14/07/2014
1.493133
14/07/2014
105.36
14/07/2014
1.0047
15/07/2014
1.288976
15/07/2014
1.506233
15/07/2014
105.49
15/07/2014
0.99815
16/07/2014
1.262319
16/07/2014
1.519333
16/07/2014
105.62
16/07/2014
0.9916
17/07/2014
1.235662
17/07/2014
1.532433
17/07/2014
105.75
17/07/2014
0.98505
18/07/2014
1.209005
18/07/2014
1.545533
18/07/2014
105.88
18/07/2014
0.9785
19/07/2014
1.182348
19/07/2014
1.558633
19/07/2014
106.01
19/07/2014
0.97195

<tbody>
</tbody>

I want to run a regression analysis between 2 pairs of currencies, dependent on what I specify, with an outcome using t-test to see whether results are significant or not. I want this to be run via macro, where I type in an input cell the 2 pairs I wish to run the regression analysis on, the macro recognises the pairs I write and runs a regression for this based on those pairs 'rate' data. Then using t statistics produces an output saying whether it is significant or no (correlation).

Finally, whatever I choose to run a regression on, in a third sheet the history of outcomes are listed and accumulates as a database every time I run the macro for a new attempt. Seems like a lot but any help would be appreciated!

Thanks
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Haven't started a macro code yet, not really sure on where to start?! A lot of things to take into account so any ideas/help would be great!
 
Upvote 0
Sub Regression()
'
' Regression Macro
'


'
Application.DisplayAlerts = False
Sheets("Data").Select
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$B$3:$B$" & Range("B" & Rows.Count).End(xlUp).Row), _
ActiveSheet.Range("$E$3:$E$" & Range("E" & Rows.Count).End(xlUp).Row), False, True, , "Data", False, _
False, False, False, , False
Application.DisplayAlerts = True
End Sub


This is what I have done to run the regression, next step is to identify what pairs I am inputting an corresponding to that, the regression is run on those pairs. Also, with the regression it produces the ouput on a new sheet, is there a way that I can produce the ouput on a specific sheet for instance called "ouputs" every single time?
 
Upvote 0
Assuming the 1st currency description is in cell M1 and the 2nd currency description is in cell N1, the code below will do the regression analysis and dump the output to cell P1 on the Data sheet.

Code: 
Sub Regression()

    Application.DisplayAlerts = False
    Sheets("Data").Select
    
    cx_desc = Range("M1")
    cx = 0
    Do
        cx = cx + 1
        DoEvents
    Loop Until Cells(1, cx) = cx_desc
    cx = num2col(cx + 1)
    
    cy_desc = Range("N1")
    cy = 0
    Do
        cy = cy + 1
        DoEvents
    Loop Until Cells(1, cy) = cy_desc
    cy = num2col(cy + 1)
    
    Application.Run "ATPVBAEN.XLAM!Regress", _
        ActiveSheet.Range("$" & cx & "$3:$" & cx & "$" & Range(cx & Rows.Count).End(xlUp).Row), _
        ActiveSheet.Range("$" & cy & "$3:$" & cy & "$" & Range(cy & Rows.Count).End(xlUp).Row), _
        False, False, , ActiveSheet.Range("$P$1"), _
        False, False, False, False, , False
    
    Application.DisplayAlerts = True

End Sub

Public Function num2col(ByVal mynumber As Integer) As String

    If mynumber < 27 Then
        num2col = Chr(mynumber + 64)
        Exit Function
    End If
    
    If (mynumber / 26) = Int(mynumber / 26) Then
        t = mynumber / 26 - 1
        num2col = Chr(t + 64) & "Z"
    Else
        t = Int(mynumber / 26)
        s = mynumber - (t * 26)
        num2col = Chr(t + 64) & Chr(s + 64)
    End If

End Function
 
Upvote 0
The following will also dump the regression results to a sheet named "Regression History":

Code: 
Sub Regression()

    Application.DisplayAlerts = False
    Sheets("Data").Select
    
    cx_desc = Range("M1")
    cx = 0
    Do
        cx = cx + 1
        DoEvents
    Loop Until Cells(1, cx) = cx_desc
    cx = num2col(cx + 1)
    
    cy_desc = Range("N1")
    cy = 0
    Do
        cy = cy + 1
        DoEvents
    Loop Until Cells(1, cy) = cy_desc
    cy = num2col(cy + 1)
    
    Application.DisplayAlerts = False
    Application.Run "ATPVBAEN.XLAM!Regress", _
        ActiveSheet.Range("$" & cx & "$3:$" & cx & "$" & Range(cx & Rows.Count).End(xlUp).Row), _
        ActiveSheet.Range("$" & cy & "$3:$" & cy & "$" & Range(cy & Rows.Count).End(xlUp).Row), _
        False, False, , ActiveSheet.Range("$P$1"), _
        False, False, False, False, , False
    
    SaveRegressionToHistory cx_desc, cy_desc
    
    Application.DisplayAlerts = True

End Sub

Public Sub SaveRegressionToHistory(ByVal Currency1 As String, ByVal Currency2 As String)

    Set ssheet = Sheets("Data")
    Set dsheet = Sheets("Regression History")

    dr = 0
    Do
        dr = dr + 1
        DoEvents
    Loop Until Sheets("Regression History").Cells(dr, 1) = ""

    If dr = 1 Then
        dsheet.Cells(1, 1) = "Currency 1"
        dsheet.Cells(1, 2) = "Currency 2"
        dsheet.Cells(1, 3) = "Multiple R"
        dsheet.Cells(1, 4) = "R Square"
        dsheet.Cells(1, 5) = "Adjusted R Square"
        dsheet.Cells(1, 6) = "Standard Error"
        dsheet.Cells(1, 7) = "Observations"
        dsheet.Cells(1, 8) = "Regression df"
        dsheet.Cells(1, 9) = "Regression SS"
        dsheet.Cells(1, 10) = "Regression MS"
        dsheet.Cells(1, 11) = "Regression F"
        dsheet.Cells(1, 12) = "Regression Significance F"
        dsheet.Cells(1, 13) = "Residual df"
        dsheet.Cells(1, 14) = "Residual SS"
        dsheet.Cells(1, 15) = "Residual MS"
        dsheet.Cells(1, 16) = "Total df"
        dsheet.Cells(1, 17) = "Total SS"
        dsheet.Cells(1, 18) = "Intercept Coefficients"
        dsheet.Cells(1, 19) = "Intercept Standard Error"
        dsheet.Cells(1, 20) = "Intercept t Stat"
        dsheet.Cells(1, 21) = "Intercept p-value"
        dsheet.Cells(1, 22) = "Intercept Lower 95%"
        dsheet.Cells(1, 23) = "Intercept Upper 95%"
        dsheet.Cells(1, 24) = "X Variable 1 Coefficients"
        dsheet.Cells(1, 25) = "X Variable 1 Standard Error"
        dsheet.Cells(1, 26) = "X Variable 1 t Stat"
        dsheet.Cells(1, 27) = "X Variable 1 p-value"
        dsheet.Cells(1, 28) = "X Variable 1 Lower 95%"
        dsheet.Cells(1, 29) = "X Variable 1 Upper 95%"
        dsheet.Cells.EntireColumn.AutoFit
        dr = 2
    End If
    
    dsheet.Cells(dr, 1) = Currency1
    dsheet.Cells(dr, 2) = Currency2
    dsheet.Cells(dr, 3) = ssheet.Range("Q4")
    dsheet.Cells(dr, 4) = ssheet.Range("Q5")
    dsheet.Cells(dr, 5) = ssheet.Range("Q6")
    dsheet.Cells(dr, 6) = ssheet.Range("Q7")
    dsheet.Cells(dr, 7) = ssheet.Range("Q8")
    dsheet.Cells(dr, 8) = ssheet.Range("Q12")
    dsheet.Cells(dr, 9) = ssheet.Range("R12")
    dsheet.Cells(dr, 10) = ssheet.Range("S12")
    dsheet.Cells(dr, 11) = ssheet.Range("T12")
    dsheet.Cells(dr, 12) = ssheet.Range("U12")
    dsheet.Cells(dr, 13) = ssheet.Range("Q13")
    dsheet.Cells(dr, 14) = ssheet.Range("R13")
    dsheet.Cells(dr, 15) = ssheet.Range("S13")
    dsheet.Cells(dr, 16) = ssheet.Range("Q14")
    dsheet.Cells(dr, 17) = ssheet.Range("R14")
    dsheet.Cells(dr, 18) = ssheet.Range("Q17")
    dsheet.Cells(dr, 19) = ssheet.Range("R17")
    dsheet.Cells(dr, 20) = ssheet.Range("S17")
    dsheet.Cells(dr, 21) = ssheet.Range("T17")
    dsheet.Cells(dr, 22) = ssheet.Range("U17")
    dsheet.Cells(dr, 23) = ssheet.Range("V17")
    dsheet.Cells(dr, 24) = ssheet.Range("Q18")
    dsheet.Cells(dr, 25) = ssheet.Range("R18")
    dsheet.Cells(dr, 26) = ssheet.Range("S18")
    dsheet.Cells(dr, 27) = ssheet.Range("T18")
    dsheet.Cells(dr, 28) = ssheet.Range("U18")
    dsheet.Cells(dr, 29) = ssheet.Range("V18")
    
    ssheet.Columns("P:V").Clear

End Sub

Public Function num2col(ByVal mynumber As Integer) As String

    If mynumber < 27 Then
        num2col = Chr(mynumber + 64)
        Exit Function
    End If
    
    If (mynumber / 26) = Int(mynumber / 26) Then
        t = mynumber / 26 - 1
        num2col = Chr(t + 64) & "Z"
    Else
        t = Int(mynumber / 26)
        s = mynumber - (t * 26)
        num2col = Chr(t + 64) & Chr(s + 64)
    End If

End Function
 
Upvote 0
Thank you very much mjbeam, it seems to work fine! Just for my own knowledge, could you explain how the last part of the code works, whats the analysis behind it?

Public Function num2col(ByVal mynumber As Integer) As String


If mynumber < 27 Then
num2col = Chr(mynumber + 64)
Exit Function
End If

If (mynumber / 26) = Int(mynumber / 26) Then
t = mynumber / 26 - 1
num2col = Chr(t + 64) & "Z"
Else
t = Int(mynumber / 26)
s = mynumber - (t * 26)
num2col = Chr(t + 64) & Chr(s + 64)
End If


End Function
 
Upvote 0
This code takes a number and converts it into a character corresponding to an Excel column. For example, the first column is column A. The ASCII code for the letter A is 65. So to convert 1 to an A, we just add 64 to it and retrieve the letter corresponding with the number 65 like this: Chr(65)

Some of the columns are made up of more than one letter. For example, column 27 is AA. The second section does some additional calculations to return the two letter code for the column.

In most cases you don't need to get the letter for a column because you can refer to the column with a number in VBA, but sometimes it is easier to work with the letter, as in the case of the regression analysis.
 
Upvote 0
Okay I get it now, where are these range cells coming from?

dsheet.Cells(dr, 1) = Currency1 dsheet.Cells(dr, 2) = Currency2 dsheet.Cells(dr, 3) = ssheet.Range("Q4") dsheet.Cells(dr, 4) = ssheet.Range("Q5") dsheet.Cells(dr, 5) = ssheet.Range("Q6") dsheet.Cells(dr, 6) = ssheet.Range("Q7") dsheet.Cells(dr, 7) = ssheet.Range("Q8") dsheet.Cells(dr, 8) = ssheet.Range("Q12") dsheet.Cells(dr, 9) = ssheet.Range("R12") dsheet.Cells(dr, 10) = ssheet.Range("S12") dsheet.Cells(dr, 11) = ssheet.Range("T12") dsheet.Cells(dr, 12) = ssheet.Range("U12") dsheet.Cells(dr, 13) = ssheet.Range("Q13") dsheet.Cells(dr, 14) = ssheet.Range("R13") dsheet.Cells(dr, 15) = ssheet.Range("S13") dsheet.Cells(dr, 16) = ssheet.Range("Q14") dsheet.Cells(dr, 17) = ssheet.Range("R14") dsheet.Cells(dr, 18) = ssheet.Range("Q17") dsheet.Cells(dr, 19) = ssheet.Range("R17") dsheet.Cells(dr, 20) = ssheet.Range("S17") dsheet.Cells(dr, 21) = ssheet.Range("T17") dsheet.Cells(dr, 22) = ssheet.Range("U17") dsheet.Cells(dr, 23) = ssheet.Range("V17") dsheet.Cells(dr, 24) = ssheet.Range("Q18") dsheet.Cells(dr, 25) = ssheet.Range("R18") dsheet.Cells(dr, 26) = ssheet.Range("S18") dsheet.Cells(dr, 27) = ssheet.Range("T18") dsheet.Cells(dr, 28) = ssheet.Range("U18") dsheet.Cells(dr, 29) = ssheet.Range("V18")</pre>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Conditional formatting based on dates
Replies
5
Views
136
JamesCanale
JamesCanale
F
  • Question
Need a formula.
Replies
1
Views
110
Peter_SSs
Peter_SSs
A
  • Question
Formula To highlight dates due within six months of the date
Replies
2
Views
287
AndrewMartin1
A
B
  • Question
Consider Blank Cell in Calculation
Replies
1
Views
55
Joe4
Joe4
B
  • Solved
VBA code for adding in text based on another cell's time.
Replies
9
Views
147
rlv01
R

Forum statistics

Threads
1,203,521
Messages
6,055,885
Members
444,830
Latest member
Excelsmallbusinessmom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top