Excel code to analyze ACSPBLS Data

Posted on July 8, 2009 by clsnyder

Import from the ACSPBLS Site

* Introduction
Many pediatric surgeons are now storing or will soon be storing a record of their operative cases on the American College of Surgeons Database, located here

It is useful to be able to analyze your own data, for volume, RVU’s over time, etc. This brief tutorial will show you how to download your stored data, run a single macro on MS Excel 2007 (It should work on Excel 2003), and create a graphic of the result.

* Description
1. Go to the site and sign in
2. On the main page, select Cases > Export
3. A new page will open. Select “All Dates”, and “Exclude ICD/CPT Descriptions”
4. Hit “Submit”
5. Save the resultant CSV (=comma separated values) file as “cases-dump.csv”, where you can find it
6. Download this file of RVU’s for each CPT code
7. Open MS Excel and open this file; rename the worksheet tab the data is on as “rvu”
8. Save the file as a “Macro-enabled” Workbook
9. Click the circular windows icon in the left upper corner of the worksheet – there is a vertical menu “New”, “Open”, “Save”, etc; at the bottom are two rectangular buttons – click on “Excel Options”
10. Select “Popular” if not already open, and pick the “Show developer tab in the ribbon”; click OK
11. Now click on the new Developer tabbed menu at the top right
12. Click on the Macro button
13. Type in “import” in the name box, and click “create”
14. A new Visual basic window will open, with this filled in:

1
2
3

Sub import()
 
End Sub

15. Delete this and past in the code from here:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112

Sub import()
	'imports from the
	Dim LastRow As Long
    Dim LastColumn As Long
    Dim FName As Variant
 
    'Query for the file to open
    FName = Application.GetOpenFilename()
    If FName = False Then
        MsgBox "You didn't choose a file"
    Else
        'MsgBox FName
    End If
 
    'Add a new sheet and import the csv data
    Sheets.Add
    ActiveSheet.Name = "cases-dump"
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & FName, _
        Destination:=Range("$A$1"))
        .Name = "cases-dump"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
 
    'Find the last Filled row of the range
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        MsgBox "Importing:  " & LastRow & "  Operations"
    End If
    Range("A:D,F:F,G:G,H:I,K:M,O:P,V:AI,AO:AZ").Select
    Selection.delete Shift:=xlToLeft
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Select
    Selection.Cut
    Columns("A:A").Select
    ActiveSheet.Paste
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.delete Shift:=xlToLeft
    Selection.Cut
    Columns("N:N").Select
    ActiveSheet.Paste
    Columns("C:C").Select
    Selection.delete Shift:=xlToLeft
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "rvu"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],rvu!R1C[-13]:R7240C[-12],2)"
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N" & LastRow), Type:=xlFillDefault
	Columns("N:N").Select
    Selection.Copy
    Columns("O:O").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.delete Shift:=xlToLeft
    Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Range("A6").Select
    	LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
	LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 
 
    Sheets.Add.Name = "pivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "cases-dump!R1C1:R" & LastRow & "C" & LastColumn, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Procedure Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("rvu"), "Sum of rvu", xlSum
    Range("A6").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, True, False, True)
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Years")
        .Orientation = xlColumnField
        .Position = 1
    End With
End Sub

16. Save and Close the Visual basic window
17. Click anywhere (single-click) in the rvu sheet
18. Run the “import” macro from the Developer tab
19. You should see this:

table_report

20. You can click in the table and then select the “Options” tab, and a column graph to get this to get this (RVU’s generated by month and year):

Op Data July 2009

21. Good Luck!

This entry was posted in Excel. Bookmark the permalink.

Comments are closed.