Massaging Stake Quarterly Report Data

Discuss where to obtain or how to fill out specific reports or forms.
mpolder-p40
New Member
Posts: 4
Joined: Mon Nov 19, 2007 2:12 pm

Massaging Stake Quarterly Report Data

Postby mpolder-p40 » Wed May 13, 2009 5:36 pm

I recently downloaded the Quarterly Report data for our Stake for the last five quarters. The data is listed across the top by unit. I found it more helpful to view each unit individually by time, so I wrote a simple awk script to rearrange it (found below). I then found a Visual Basic macro for Excel that read each unit into its own sheet in a single workbook. (original script at http://excel.tips.net/Pages/T003148_Importing_Multiple_Files_to_a_Single_Workbook.html)

My hope in posting this here is that some Visual Basic genius will take this script and figure out how to automatically make graphs for each unit so you can see trending information.

matthew

awk script:

Code: Select all

BEGIN { FS = "\t"; fileIndex = 1 }
NR == 1 {
    for (i=3; i<=NF-1; i++)
        unitName[i-2] = $i

    filename = FILENAME
    nUnits = NF-3
}
$1 >= 1 && $1 <= 24 {
    if ( FILENAME != filename )
    {
        fileIndex++
        filename = FILENAME
    }
    stat = $1
    for (unit=3; unit<=NF; unit++)
        data[unit-2, stat, fileIndex] = $unit
    #print filename, $0 > "apple.txt"
}
END {
    for (unit=1; unit<=nUnits; unit++)
    {
        name = unitName[unit]
        filename = "unitStats/" tolower(name) ".txt"
        print "Members" > filename
        for (stat=1; stat<=24; stat++)
        {
            str = GetCategory(stat)
            if ( str != "none" )
                print str > filename

            printf( "%s", GetStat(stat) ) > filename
            for (k=1; k<=fileIndex; k++)
            {
                printf(",%d", data[unit, stat, k]) > filename
                print data[unit, stat, k] > "banana.txt"
            }
            printf("\n") > filename
        }
        close(filename)
    }
}

function GetStat(i) {

    switch ( i ) {
        case 1:
            str = "Total members"
            break
        case 2:
            str = "Average Sacrament meeting attendance"
            break
        case 3:
            str = "Total families"
            break
        case 4:
            str = "Total families visited by home teachers"
            break
        case 5:
            str = "Total Melchizedek Priesthood holders"
            break
        case 6:
            str = "Total Melchizedek Priesthood holders attending priesthood meetings"
            break
        case 7:
            str = "Total prospective elders"
            break
        case 8:
            str = "Total prospective elders attending priesthood meetings"
            break
        case 9:
            str = "Total women"
            break
        case 10:
            str = "Total women attending Sunday Relief Society meetings"
            break
        case 11:
            str = "Total women contacted by visiting teachers"
            break
        case 12:
            str = "Total endowed adults"
            break
        case 13:
            str = "Total endowed adults with a temple recommend"
            break
        case 14:
            str = "Total Young Men"
            break
        case 15:
            str = "Total Young Men attending priesthood meetings"
            break
        case 16:
            str = "Total Young Women"
            break
        case 17:
            str = "Total Young Women attending Sunday Young Women meetings"
            break
        case 18:
            str = "Total children ages 3 (as of 1 January) through 11 years"
            break
        case 19:
            str = "Total children on line 18 attending Sunday Primary meetings"
            break
        case 20:
            str = "Total children ages 0 through 2 (as of 1 January) years"
            break
        case 21:
            str = "Total converts age 8 and older baptized and confirmed in the last 12 months"
            break
        case 22:
            str = "Converts on line 21 attending at least one sacrament meeting last month"
            break
        case 23:
            str = "Converts age 12 and older who have a Church responsibility or calling"
            break
        case 24:
            str = "Convert males age 12 and older who hold the Aaronic Priesthood"
            break
        }

    return str
}

function GetCategory(i) {
   
    switch ( i ) {
        case 3:
            str = "Families"
            break
        case 5:
            str = "Adults"
            break
        case 14:
            str = "Youth"
            break
        case 18:
            str = "Children"
            break
        case 21:
            str = "Converts"
            break
        default:
            str = "none"
    }

    return str
}
Visual Basic script:

Code: Select all

Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    sDelimiter = ","

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
    wkbTemp.Sheets(1).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=False, _
      Other:=True, OtherChar:="|"
    x = x + 1

    While x <= UBound(FilesToOpen)
        Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
        With wkbAll
            wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
            .Worksheets(x).Columns("A:A").TextToColumns _
              Destination:=Range("A1"), DataType:=xlDelimited, _
              TextQualifier:=xlDoubleQuote, _
              ConsecutiveDelimiter:=False, _
              Tab:=False, Semicolon:=False, _
              Comma:=False, Space:=False, _
              Other:=True, OtherChar:=sDelimiter
        End With
        x = x + 1
    Wend

ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

User avatar
mkmurray
Senior Member
Posts: 3241
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

Postby mkmurray » Wed May 13, 2009 5:53 pm

What's an "awk" script?

From what I'm gathering, let me ask a quick question...do these type of scripts (awk) only run on Unix-based systems? If that's true, that will narraw your audience of who could benefit from your idea.
Many questions are already answered on the LDSTech wiki. Check it out!

russellhltn
Community Administrator
Posts: 20757
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

Postby russellhltn » Wed May 13, 2009 6:01 pm

And I think the VB Script excludes using it in Open Office. I think OO uses Python for scripting. Or can this VB Script run stand-alone?
Have you searched the Wiki?
Try using a Google search by adding "site:tech.lds.org/wiki" to the search criteria.

User avatar
aebrown
Community Administrator
Posts: 14691
Joined: Tue Nov 27, 2007 8:48 pm
Location: Sandy, Utah

Postby aebrown » Wed May 13, 2009 7:41 pm

mkmurray wrote:What's an "awk" script?

From what I'm gathering, let me ask a quick question...do these type of scripts (awk) only run on Unix-based systems? If that's true, that will narraw your audience of who could benefit from your idea.


AWK is a general-purpose scanner and pattern matcher. It was originally developed under Unix (AWK stands for Aho, Weinberger, and Kernighan, three of the great pioneers of Unix and related tools at Bell Labs), but a Windows implementation is available at sourceforge.

jdlessley
Community Moderators
Posts: 6526
Joined: Sun Mar 16, 2008 11:30 pm
Location: USA, TX

Postby jdlessley » Wed May 13, 2009 8:53 pm

mpolder wrote:My hope in posting this here is that some Visual Basic genius will take this script and figure out how to automatically make graphs for each unit so you can see trending information.
I gather from this statement that you have little to no experience in Visual Basic for Applications (VBA - the scripting language used in the Microsoft Office suite which is similar syntactically to, but perform differently from, the programming language Visual Basic).

While I have some experience in using VBA it is quite difficult to know what you are looking for. The graphing features of Microsoft Excel provide such a wide spectrum of possible combinations that you need to provide more information about the expected output as well as the data to be used.

My recommendation is to use the macro recording feature of Excel while creating a graph from the data. You can then clean up the script results in the visual basic editor. If you are not proficient with VBA then you can post the resultant macro script here and let someone help you clean it up to get exactly the results you want.
JD Lessley
Have you tried finding your answer on the LDS.org Help Center page or the LDSTech wiki?


Return to “Reports and Forms”

Who is online

Users browsing this forum: No registered users and 1 guest