The Voice of Reason

As you probably know, Excel lacks many sophisticated analytic techniques and not everyone has access to a true analytics package like STATA or SPSS. So what can you do if you’re modeling data and trying to decide of the type of model? Will a linear model work or do you need to consider an exponential or polynomial model to explain your dataset? Picking the wrong model is a mistake that sometimes even the best-intentioned researchers make and the incorrect model can even end up being published. I’m going to cover one technique called the Durbin-Watson test and explain how to use it to tell if a linear regression model is a valid model for your dataset.

First off, you can use Excel to add a linear trendline to your data. Make sure you have Excel give you the equation for the trendline – we’ll need this to compute the Durbin-Watson.

Let’s say you’ve run the regression and ended up with the formula y = 0.1897x + 5.0517. The next step is to calculate your fitted values.

For the next two steps (Yhat and residuals), you can either get these with Excel or calculate them manually. I’ll cover the manual approach.

If you first point was (2, 6) then the Yhat (fitted value) is 2*.1897 + 5.0517 = 5.4311. Do this for all of your data points.

The next step is to calculate residual values. The residual value is simply the real value minus the fitted value. So, for our above example, the residual value is 6 – 5.4311 = 0.5689.

The next step is to calculate the squared difference between successive residuals, summed from the second observation to the nth observation. So, for all the residuals in your list starting with the second one, take that residual, subtract out the previous residual, and square the result. Then sum all of these values.

The next to last step is to calculate the sum of squares residual. For this, calculate the square of each individual residual and the sum the entire range. This is your sum of squares.

Finally, to calculate the Durbin-Watson statistic, divide the squared difference of successive residuals by the sum of squares. This value can then be compared to online lookup tables (such as at http://www.paolocoletti.it/statistics/exercises/Durbin-Watson.html) to see if a linear model is appropriate.

For example, if you have one independent variable and 15 observations (k = 1, n = 15), then dL = 1.08 and dU = 1.36. If your value is between this range, then a linear model is probably appropriate for your data.

However, if your value was for example 0.9, then your model exhibits positive autocorrelation (i.e. the errors are not independent) and you can not use a linear model to describe the data. You then have to consider other models or transform the data to normality (natural log, square root, etc.) before modeling and re-compute the Durbin-Watson.

Thanks for reading this brief tutorial on data modeling and good luck with your analyses!

Filed under: Programming, Technology | Leave a comment »

I’m still on a programming kick with my blog, so here’s another article. I used to run into this problem a lot so for those of you that are or know Excel users, here’s my solution. The below code is run as a VBA script in Excel.

Here’s a problem you may face. You ask someone for information and they send it to you in an Excel sheet. You then happily open the sheet only to find that it’s not in an easy-to-use format.

For example, let’s say you had an Excel sheet with several hundred business IDs and each business ID has a code set that you need to use.

Ex: A | B | C
ID | Business Name | Codes
72566477 | McDonald’s | Chicken_Sandwich,Fries,Diet_Coke,etc.

If you wanted to make a table out of these, you could do a text-to-column and then transpose but, obviously, this isn’t a good approach for any but the smallest datasets.The code below will take these lists, create a new excel sheet, and then match up the static values with each item in the comma-seperated list. Within seconds, you can have a useable dataset instead of a bunch of CSVs.

Public Function CallF()

Dim Keys(0 To 2) As Long
Keys(0) = 1
Keys(1) = 2
Keys(2) = 3

CSVToTable "Sheet to Convert", 4, 4, Keys, True, 3

End Function

Public Function CSVToTable(WkSheetName As String, StartRow As Long, CSVColumn As Long, KeyColumns As Variant, Optional HasHeader As Boolean = False, Optional HeaderRow As Long = 0)

Dim x As Long, y As Long, z As Long, sSheetRow As Long
Dim ParsedValues As Variant
Dim LockValues() As String
Dim sSheet As Worksheet
Dim tSheet As Worksheet
Set sSheet = Worksheets(WkSheetName)
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Set tSheet = Worksheets(Application.Worksheets.Count)
tSheet.Name = "Table"
y = 1

'Read the columns to the new sheet
If HasHeader = True And HeaderRow > 0 Then
'Add the header columns
For x = LBound(KeyColumns) To UBound(KeyColumns)
'Format the column
tSheet.Columns.EntireColumn.NumberFormat = "@"
tSheet.Cells(1, y).Value = sSheet.Cells(HeaderRow, KeyColumns(x)).Value
y = y + 1
Next x
'Add the CSV column
tSheet.Cells(1, y).Value = sSheet.Cells(HeaderRow, CSVColumn).Value
'Reset y for row values
y = 2
End If

'Now read off the data
sSheetRow = HeaderRow + 1
Do While IsNull(sSheet.Cells(sSheetRow, KeyColumns(0))) = False And sSheet.Cells(sSheetRow, KeyColumns(0)) <> ""
ReDim LockValues(UBound(KeyColumns))
'Read in the lock values
For z = LBound(KeyColumns) To UBound(KeyColumns)
LockValues(z) = CStr(sSheet.Cells(sSheetRow, KeyColumns(z)))
Next z
'Parse the CSV
ParsedValues = Split(sSheet.Cells(sSheetRow, CSVColumn), ",")
If IsArray(ParsedValues) And UBound(ParsedValues) <> -1 Then
For x = LBound(ParsedValues) To UBound(ParsedValues)
'For each entry, print the static values and the parsed CSV value
'Print the lock values first
For z = 1 To UBound(LockValues) + 1
tSheet.Cells(y, z) = Trim(CStr(LockValues(z - 1)))
Next z
'Now print the CSV value - y is the row number, z is the column number
tSheet.Cells(y, z) = Trim(CStr(ParsedValues(x)))
'Increment the row
y = y + 1
Next x
Else
'Increment the table sheet row
y = y + 1
End If
'Increment the source sheet row
sSheetRow = sSheetRow + 1
Loop

'Format the table
With tSheet.Cells.EntireColumn
.Font.Name = "Arial"
.Font.Size = 8
.AutoFit
End With

'Close the objects
Set sSheet = Nothing
Set tSheet = Nothing

End Function

Filed under: Programming, Technology | Leave a comment »

As any database designer knows, MS Access is a quick and easy way to get a user application up and running. It’s intuitive, easy to use, easy to learn and provides a good, low-cost user experience. However, the built-in security for Access is often cumbersome and easy to beat. If you are in an active directory domain environment, the best course of action is to use the active directory to allocate access based on users and security groups.

The script below will allow you to authenticate users via the active directory. While you may not have access to create security groups, you can authenticate and then match against an encrypted user table (MD5, AES, Blowfish, etc though MD5 should not be considered without using a salt). The security advantage here is that you are limiting potential logins both via the active diretory and the user table. Also, users will not have to remember a password for your system and for the network. As the user updates his/her network password, that same update is reflected with your database system login.

Here’s the code for my solution to this problem (using the Active DS Type Library)

Public Function ADSAuth(ByVal ADSDomain As String, ByVal ADSUsername As String, ByVal ADSPassword As String) As Boolean
On Error GoTo ErrHandler

Dim dso As IADsOpenDSObject
Dim Domain As IADsDomain
Set dso = GetObject("WinNT:")
Set Domain = dso.OpenDSObject("WinNT://" & ADSDomain, ADSUsername, ADSPassword, ADS_SECURE_AUTHENTICATION)
Set Domain = Nothing
Set dso = Nothing
ADSAuth = True
Exit Function

ErrHandler:
If Err.Number = -2147023570 Then
 MsgBox "Login Failure: Unknown user name or bad password.", vbCritical + vbOKOnly, "Login Failure"
Else
 MsgBox "Program encountered error number " & Err.Number & " : " & Err.Description & ".", vbCritical + vbOKOnly, "Program Error"
End If
Set Domain = Nothing
Set dso = Nothing
ADSAuth = False

End Function

This is just a brief, single purpose script I threw together so if anyone has a faster/better way of doing this or has the code for a more comprehensive manipulation of the active directory, post it in the comments. Thanks for reading and happy coding!

Filed under: Programming, Technology | Leave a comment »