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
[TABLE="width: 801"]
<tbody>[TR]
[TD]GBP/USD
[/TD]
[TD][/TD]
[TD][/TD]
[TD]EUR/USD
[/TD]
[TD][/TD]
[TD][/TD]
[TD]USD/JPY
[/TD]
[TD][/TD]
[TD][/TD]
[TD]USD/CAD
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]RATE
[/TD]
[TD][/TD]
[TD]DATE
[/TD]
[TD]RATE
[/TD]
[TD][/TD]
[TD]DATE
[/TD]
[TD]RATE
[/TD]
[TD][/TD]
[TD]DATE
[/TD]
[TD]RATE
[/TD]
[/TR]
[TR]
[TD]01/07/2014
[/TD]
[TD]1.6475
[/TD]
[TD][/TD]
[TD]01/07/2014
[/TD]
[TD]1.3178
[/TD]
[TD][/TD]
[TD]01/07/2014
[/TD]
[TD]103.67
[/TD]
[TD][/TD]
[TD]01/07/2014
[/TD]
[TD]1.092
[/TD]
[/TR]
[TR]
[TD]02/07/2014
[/TD]
[TD]1.6409
[/TD]
[TD][/TD]
[TD]02/07/2014
[/TD]
[TD]1.346
[/TD]
[TD][/TD]
[TD]02/07/2014
[/TD]
[TD]103.8
[/TD]
[TD][/TD]
[TD]02/07/2014
[/TD]
[TD]1.079
[/TD]
[/TR]
[TR]
[TD]03/07/2014
[/TD]
[TD]1.6578
[/TD]
[TD][/TD]
[TD]03/07/2014
[/TD]
[TD]1.344
[/TD]
[TD][/TD]
[TD]03/07/2014
[/TD]
[TD]103.93
[/TD]
[TD][/TD]
[TD]03/07/2014
[/TD]
[TD]1.0789
[/TD]
[/TR]
[TR]
[TD]04/07/2014
[/TD]
[TD]1.658
[/TD]
[TD][/TD]
[TD]04/07/2014
[/TD]
[TD]1.362133
[/TD]
[TD][/TD]
[TD]04/07/2014
[/TD]
[TD]104.06
[/TD]
[TD][/TD]
[TD]04/07/2014
[/TD]
[TD]1.0702
[/TD]
[/TR]
[TR]
[TD]05/07/2014
[/TD]
[TD]1.309
[/TD]
[TD][/TD]
[TD]05/07/2014
[/TD]
[TD]1.375233
[/TD]
[TD][/TD]
[TD]05/07/2014
[/TD]
[TD]104.19
[/TD]
[TD][/TD]
[TD]05/07/2014
[/TD]
[TD]1.06365
[/TD]
[/TR]
[TR]
[TD]06/07/2014
[/TD]
[TD]1.66
[/TD]
[TD][/TD]
[TD]06/07/2014
[/TD]
[TD]1.388333
[/TD]
[TD][/TD]
[TD]06/07/2014
[/TD]
[TD]104.32
[/TD]
[TD][/TD]
[TD]06/07/2014
[/TD]
[TD]1.0571
[/TD]
[/TR]
[TR]
[TD]07/07/2014
[/TD]
[TD]1.502233
[/TD]
[TD][/TD]
[TD]07/07/2014
[/TD]
[TD]1.401433
[/TD]
[TD][/TD]
[TD]07/07/2014
[/TD]
[TD]104.45
[/TD]
[TD][/TD]
[TD]07/07/2014
[/TD]
[TD]1.05055
[/TD]
[/TR]
[TR]
[TD]08/07/2014
[/TD]
[TD]1.475576
[/TD]
[TD][/TD]
[TD]08/07/2014
[/TD]
[TD]1.414533
[/TD]
[TD][/TD]
[TD]08/07/2014
[/TD]
[TD]104.58
[/TD]
[TD][/TD]
[TD]08/07/2014
[/TD]
[TD]1.044
[/TD]
[/TR]
[TR]
[TD]09/07/2014
[/TD]
[TD]1.448919
[/TD]
[TD][/TD]
[TD]09/07/2014
[/TD]
[TD]1.427633
[/TD]
[TD][/TD]
[TD]09/07/2014
[/TD]
[TD]104.71
[/TD]
[TD][/TD]
[TD]09/07/2014
[/TD]
[TD]1.03745
[/TD]
[/TR]
[TR]
[TD]10/07/2014
[/TD]
[TD]1.422262
[/TD]
[TD][/TD]
[TD]10/07/2014
[/TD]
[TD]1.440733
[/TD]
[TD][/TD]
[TD]10/07/2014
[/TD]
[TD]104.84
[/TD]
[TD][/TD]
[TD]10/07/2014
[/TD]
[TD]1.0309
[/TD]
[/TR]
[TR]
[TD]11/07/2014
[/TD]
[TD]1.395605
[/TD]
[TD][/TD]
[TD]11/07/2014
[/TD]
[TD]1.453833
[/TD]
[TD][/TD]
[TD]11/07/2014
[/TD]
[TD]104.97
[/TD]
[TD][/TD]
[TD]11/07/2014
[/TD]
[TD]1.02435
[/TD]
[/TR]
[TR]
[TD]12/07/2014
[/TD]
[TD]1.368948
[/TD]
[TD][/TD]
[TD]12/07/2014
[/TD]
[TD]1.466933
[/TD]
[TD][/TD]
[TD]12/07/2014
[/TD]
[TD]105.1
[/TD]
[TD][/TD]
[TD]12/07/2014
[/TD]
[TD]1.0178
[/TD]
[/TR]
[TR]
[TD]13/07/2014
[/TD]
[TD]1.34229
[/TD]
[TD][/TD]
[TD]13/07/2014
[/TD]
[TD]1.480033
[/TD]
[TD][/TD]
[TD]13/07/2014
[/TD]
[TD]105.23
[/TD]
[TD][/TD]
[TD]13/07/2014
[/TD]
[TD]1.01125
[/TD]
[/TR]
[TR]
[TD]14/07/2014
[/TD]
[TD]1.315633
[/TD]
[TD][/TD]
[TD]14/07/2014
[/TD]
[TD]1.493133
[/TD]
[TD][/TD]
[TD]14/07/2014
[/TD]
[TD]105.36
[/TD]
[TD][/TD]
[TD]14/07/2014
[/TD]
[TD]1.0047
[/TD]
[/TR]
[TR]
[TD]15/07/2014
[/TD]
[TD]1.288976
[/TD]
[TD][/TD]
[TD]15/07/2014
[/TD]
[TD]1.506233
[/TD]
[TD][/TD]
[TD]15/07/2014
[/TD]
[TD]105.49
[/TD]
[TD][/TD]
[TD]15/07/2014
[/TD]
[TD]0.99815
[/TD]
[/TR]
[TR]
[TD]16/07/2014
[/TD]
[TD]1.262319
[/TD]
[TD][/TD]
[TD]16/07/2014
[/TD]
[TD]1.519333
[/TD]
[TD][/TD]
[TD]16/07/2014
[/TD]
[TD]105.62
[/TD]
[TD][/TD]
[TD]16/07/2014
[/TD]
[TD]0.9916
[/TD]
[/TR]
[TR]
[TD]17/07/2014
[/TD]
[TD]1.235662
[/TD]
[TD][/TD]
[TD]17/07/2014
[/TD]
[TD]1.532433
[/TD]
[TD][/TD]
[TD]17/07/2014
[/TD]
[TD]105.75
[/TD]
[TD][/TD]
[TD]17/07/2014
[/TD]
[TD]0.98505
[/TD]
[/TR]
[TR]
[TD]18/07/2014
[/TD]
[TD]1.209005
[/TD]
[TD][/TD]
[TD]18/07/2014
[/TD]
[TD]1.545533
[/TD]
[TD][/TD]
[TD]18/07/2014
[/TD]
[TD]105.88
[/TD]
[TD][/TD]
[TD]18/07/2014
[/TD]
[TD]0.9785
[/TD]
[/TR]
[TR]
[TD]19/07/2014
[/TD]
[TD]1.182348
[/TD]
[TD][/TD]
[TD]19/07/2014
[/TD]
[TD]1.558633
[/TD]
[TD][/TD]
[TD]19/07/2014
[/TD]
[TD]106.01
[/TD]
[TD][/TD]
[TD]19/07/2014
[/TD]
[TD]0.97195
[/TD]
[/TR]
</tbody>[/TABLE]

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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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

K
  • Solved
Evaluate date range and return the corresponding month and year
Replies
1
Views
123
Scott Huish
Scott Huish
A
  • Solved
Count entry by time
Replies
3
Views
82
admvlad
A
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
149
Tara_A_Data
T
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
262
excelnewbie999
E
H
  • Solved
Query not pulling right info when running in VBA
2
Replies
13
Views
364
Holley
H

Forum statistics

Threads
1,222,383
Messages
6,165,660
Members
451,983
Latest member
Raph24

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