UCB Datadude (V. 1.0) User Guide

“Dude, Where’s My Data?”

UCB DataDude (v. 0.6.7) User Guide

Center for Social Services Research

University of California at Berkeley

“Dude, Where’s My Data?”…The University of California, Berkeley (UCB) DataDude is freely available for the use of California counties and others who wish to download data from the CWS/CMS reports on the Center for Social Services Research website:

The DataDude (MS Excel) and Documentation for users are available at:

Contents

1. Introduction

2. Set Up

3. Downloading the DataDude to use as a Template……………………………..……….3

4. Tour the DataDude

5. Basic Use

Part 1: Get Data

Method A: Select a Summary Report from the Dropdown Menu

Method B: Use a Sample Web Address:

Part 2: Create Summary Table for Graphing

Part 3: Create a Chart for Graph

Part 4: Create a File with Another Measure

6. Advanced Use

7. Troubleshooting

Missing Cells

Columns with Incorrect Titles

Graphing Errors: Problem One

Graphing Errors: Problem Two

Share the UCB DataDude

8. Technical Support, Questions, and Feedback

Appendix 1: Determining Versions of Windows OS and Excel

Appendix 2: Enabling Macros in Microsoft Excel

Appendix 3: Obtaining a Sample Web Address

This Guide

Welcome to the UCB DataDude User Guide for v. 0.6.7. This guide offers information about the use of the University of California at Berkeley (UCB) DataDude version 0.6.7. This guide assumes a general familiarity with the UCB CWS/CMS reports website and the analysis of child welfare data. Instructions and screen captures are based on a Microsoft (MS) Windows 2000 Pro / MS Excel XP configuration. Other configurations should follow similar sequences and have a present screen appearance, but may differ in specifics.

1. Introduction

The purpose of the UCB DataDude is to facilitate access to the information available in the CWS/CMS reports section of the Center for Social Services Research (CSSR) website. In particular, this tool allows users to review and summarize data across time periods for measures of interest. The DataDude is an Excel file that allows you to (1) download a report series from the UCB reports website, and (2) summarize selected information in a form suitable for review and/or graphing. A report series is a set of web pages that report the same information for various time periods, either a series of entry cohort reports or a series of point-in-time reports.

2. Set Up Checklist

Operating System (OS) and Software Requirements: Although resources have not allowed extensive testing, the DataDude should operate on all versions of MS Windows 95 and above and all versions of MS Excel 2000 and above.Note: The DataDude is not currently available for Excel 97.See ‘Appendix 1: Determining Versions of Windows OS and Excel’ for details. Consult Microsoft product documentation for Windows / Excel version compatibility.

MS Excel: Macros must be enabled in Excel for the DataDude to function. Please see ‘Appendix 2: Enabling Macros in Microsoft Excel’ for details.

Internet Connection: You must be connected to the Internet for Part 1 (download a report series) to function. You can use Part 2 (summarize selected information) with or without an Internet connection.

Establish a template location: Establish a safe and convenient location to store the DataDude template, such as your agency’s shared drive. If for any reason, your DataDude template (UCB_DataDude.xls) becomes corrupted or damaged, replace it with a fresh copy, downloaded from the website at:

Also, we recommend that you periodically compare the version information on the DataDude ‘controls’ page to the version on the website and download more recent version if available.

Establish a location for report files: You may want to establish a folder in which you can save various report series, summaries, graphs, etc. You may want to establish such a folder on your agency’s shared drive, on your own hard drive or private folder, or both.

3. Downloading the DataDude to use as a Template

1.Go to and right-click on the DataDude Tool*link.

Click ‘Save’ and select a convenient location.
Click ‘Open’ when the download is complete.

4. A Tour of the DataDude

Worksheets: The DataDude has three worksheets:

(A) Controls—Use this sheet to initiate download and summary functions.

(B) ReportList—Use this sheet to update the dropdown list of reports (see ‘Advanced Use’for details).

The tabs for the three worksheets are circled in red below. ‘Controls’ is currently selected:

A. ‘Controls’ Worksheet: The controls worksheet (above) has the following objects:

Part / Step / Item / Type / Restrictions
Part 1: Get Data / Step 1 / Which report do you want? / Dropdown
menu / Entry required.
Entry is limited to dropdown list.
Alternate Step 1 / Which report do you want?: Select >Use Sample Web Address from dropdown menu / Data entry / Enter web address of desired report (any time period)
Step 2 / Where do you want to save the new file? (Leave blank to save in ‘C:\UCBData\’) / Data entry / Enter path (location to save file).
Leave blank to save in ‘C: \UCBData\’.
Step 3 / Get Data / Button / None.*
Part 2: Create Summary Table for Graphing / Step 1 / Which report series do you want to use for your summary table? (For example, ‘S2’. Leave blank for ‘S1’.) / Data entry / 2 characters or blank.
Enter report series ‘S2’-’S9’.
Leave blank for ‘S1’.
Step 2 / How many title columns do you want in your summary table? (For example, ‘2’ or ‘3’. Leave blank for ‘1’.) / Data entry / Whole number between ‘1’ and‘4’ or blank. Enter ‘2’, ‘3’, or ‘4’. Leave blank for ‘1’.
Step 3 / Which Excel column do you want in your summary table? (For example, ‘C’ or ‘D’.) / Data entry / 1-2 characters.
Enter column label ‘A’-’IV’.
Step 4 / Create Summary Table / Button / None.*

* Before clicking ‘Get Data’ or ‘Create Summary Table’ button, click on white space on the screen next to the ‘Get Data’or ‘Create Summary Table’ button.
B. ‘ReportList’ Worksheet: The ‘ReportList’ worksheet is the source for the dropdown menu on the ‘Controls’ worksheet and is used by the code that makes the DataDude run. See ‘Advanced Use: Add report series to the dropdown menu’ for details of the ‘ReportList’ worksheet.

C.‘Sheet3’ Worksheet: There are no user items on the ‘Sheet3’ worksheet. The DataDude uses the ‘Sheet3’ worksheet as a placeholder. Do not delete or modify this sheet until all download functions are complete for your file.

5. Basic Use

Open the DataDude as you would any other Excel file.

Part 1: Get Data

Step 1: Indicate the report series to download

Enter the report series that you want todownloadwith one of the following two methods:

Method A: Select a summary report from the dropdown menu.

Method B: Use a sample web address:

Scroll all the way to the top of the dropdown menu and select >Use Sample Web Address
Enter the web address of any desired report (any time period).

Method A: Select a Summary Report from the Dropdown Menu

Use the drop down menu to select an AB636 report. You can use the scroll bar on the right of the dropdown list to view all report names. These preloaded reports correspond to those used to produce the UCB measures for the C-CFSR/AB636 Outcome & Accountability County Data Reports. In this example, the report, ‘4B. Point in Time’, which is the CWS/CMS Report, ‘Children in Child Welfare Supervised Foster Care by Placement Type’, is selected:

Method B: Use a Sample Web Address:

The DataDude allows you to download report series other than those listed on the dropdown menu as follows:

Under ‘Which report do you want?’ scroll to the top of the dropdown menu and select the first item, ‘>Use Sample Web Address.’ This will enable two additional action items:
‘Sample web address (See directions below)’
‘Short name of report (For use in name of new file)’.
Under ‘Sample web address’ paste in a sample web address for the report series you would like to download. To obtain a sample web address, see ‘Appendix 3: Obtaining a Sample Web Address’. After entering sample web address, hit ‘Return’ or ‘Tab.’
Under ‘Short name of report’, type in a short descriptive name for the report series. This name will be part of the file name for the new Excel file created by the tool. After entering file name, hit ‘Return’ or ‘Tab.’

Step 2: Indicate the destination path

Once you have indicated the report series to download, indicate a ‘path’ (destination folder) to which the new file will be saved. You may indicate a path such as ‘C:\CWS Report\’ or you may leave this field blank to save the new file in the default directory ‘C: \UCBData\’. If the specified path does not exist, the DataDude will offer to create the folder.After entering the destination, hit ‘Return’ or ‘Tab'. Note: In the screen capture below the specified destination path is ‘H:\Colleen\Family to Family\DataDude Data.’

Step 3: Click the ‘Get Data’ button

After you have clicked the ‘Get Data’ button, the DataDude:

(a) Assigns a filename:

‘[short form of report name]_[‘S’ for Summary, ‘0’ for CA, or county #][_version #...if required].xls’.

Method A: Assigns a preloaded short form of report name, e.g., ‘4B_PointInTime_S.xls’
Method B: Assigns a user designated short form of report name, e.g., ‘Children in Foster Care by Placement Type & Ethnicity_0.xls’

(b) Saves the file to the directory you have specified. If a file with the same name already exists in the specified location, you will see the message box:

UCB_DataDude_UserGuide_updatedAugust 2006

‘Yes’ replaces most recent version (in this case, replaces Children in Foster Care by Placement Type & Ethnicity_0.xls’).
‘No’ creates new version, ‘Children in Foster Care by Placement Type & Ethnicity_0_01.xls’.
‘Cancel’ stops process.

(d) Searches the web for the report series you have specified.

(e) Downloads the report data for each available report (i.e., each time period), to a new Excel worksheet.

What you will see:

(a) After clicking on the ‘Get Data’ button, you will see some activity on the screen as the DataDude retrieves the report information.

(b) Once this activity has stopped, you will see the same control screen as before; however, you will notice new tabs along the bottom of the Excel window (See circled tabs below).

In this example, the Data Dude has downloaded all time periods for the CWS/CMS report entitled Children in Child Welfare Supervised Foster Care by Placement Type.

Note that the S1- Jul1998 Children Welfare Supervised Foster Care by Placement Type has been selected for viewing. All other time periods can be accessed by clicking on their corresponding tabs along the bottom of the worksheet.

(c) The Excel file has been saved under a new name, according to the convention outlined above. However, the DataDude tool (UCB_DataDude.xls) is unchanged in its original location.

Note: Steps 1 through 3 can be repeated within the same file to download a different report series to the same Excel file (this feature may be used for comparison on different reports). The second series that is downloaded will have the pre-fix ‘S2’ in its worksheet name.Part 2: Create Summary Table for Graphing (using ‘Controls’ worksheet)

After using the DataDude to download a report series, return to the ‘Controls’ worksheet. Use Part 2 of the DataDude to create a new summary worksheet.

Step 1: Indicate the report series you wish to summarize

The report series is indicated by the prefix of the worksheet names, e.g., ‘S1-JUL1998’, ‘S1-OCT 1998’, ‘S1-JUL1999’, etc. are points in timeJuly 1, 1998, October 1, 1998, July 1, 1999, etc. for report series 1, i.e., ‘S1’. If you wish to chart report series ‘S1,’ then you can leave Step 1 blank. If, however, you would like to chart a different report series, enter the report series as ‘S2’ or ‘S3’, etc. In this example, you want to summarize report ‘S1’ so leave Step 1 blank.

Please note that the ‘S1’ number is given to the first DataDude download series, S2 is given to the second download series within the same Excel file, etc. (numbers assigned correspond to the order in which the report series were downloaded).

Step 2: Indicate how many title columns you want in your summary table.

Some tables will contain more than one set of row headings (i.e., title columns) that you may want to include in your summary report. For example, if the report is broken out by age and ethnicity, it may have two columns: one with age headings and one with ethnicity. If this is thecase, enter the number ‘2’in Step 2. However, most tables will have only one title column; if so, leave Step 2 blank. For example, the report below has only one title column, ‘County’, so Step 2 is blank.

Step 3: Indicate the Excel column you want in your summary table

Indicate a column using ‘A’, ‘B’, ‘C’ or ‘AA’, ‘AB’, ‘AC’, etc. Currently, the DataDude will only chart one column per report series. In this example, column ‘AB’would be entered. It indicates the total number of children in Child Welfare supervised foster care (highlighted below). After entering column letter, hit ‘enter’ or ‘tab’ on the keyboard.

Step 4: Click the ‘Create Summary Table’ button.

The DataDude:

(a) Creates a new worksheet.

(b) Copies specified title rows to the new worksheet.

(d) Pastes the columns to the new worksheet.

(e) Adds a title (date) at the top of each column.

(f) Names the new worksheet (name appears in the tab at the bottom of the screen).Note that the file name has not changed.

What you will see: The DataDude creates a summary table within a new worksheet that organizes information by month and year. In this example, the new summary table is titled ColAB_S1-Jul1998-Oct2004. This function, allows you to view specific information in a report series over time. For cohort reports, the dates represent the end dates of the entry cohorts in the summary. For point-in-time reports, the dates represent the various points in time in the summary.

Review the summarized data: Some tables may include incorrect columns or may be missing data cells. This is the result of inconsistencies between different data extractsin the web reports. (CSSR is working to standardize reports across all time periods; however, completion of this process may take several quarterly reporting cycles to complete.) You should look for:

(a) Missing cells (some columns may be shorter than others are)

(b) Columns with incorrect titles

See ‘Advanced Use: Troubleshooting’ for techniques for correcting these problems.

Once the table has been created, you may edit the table as needed for graphing and charting purposes.

Part 3: Create a Chart or Graph

Excel allows you to easily chart or graph selected information.

Select the data to chart: You can select the information you wish to chart by highlighting selected data.To select a specific row or multiple rows, you should hold down the ‘Ctrl’ key while highlighting each row.

After you select the information you wish to chart, you should click on the ‘Insert’ button located on the Excel tool bar. Choose ‘Chart’ and then follow the directions offered by the ‘Chart Wizard’ in Excel.

Note: The line graph has been selected in the Chart Wizard because it facilitates the analysis of data across time.
The graph that you create allows you to examine Child Welfare trends within your county and throughout the state. It is important that counties do not draw comparisons to performance in other counties or even to the State as a whole due to the differences in demographics, resources, and practice.

Note: Remember to save your document frequently to ensure that the downloaded data, Summary Table and Graph are saved.

Part 4: Create a File with another Measure (another report)

(a) Open the DataDude again from its original location.

(b) Follows the steps outlined above in ‘Basic Use’.

6. Advanced Use

Modify files for distribution/presentation: Use standard Excel techniques for reorganizing, editing, formatting, printing, etc. to modify DataDude files for distribution and presentation. Once you have completed the download and summary in a particular file, you can delete the ‘Controls’, ‘ReportList’, and ‘Sheet3’ worksheets. You can also delete source data worksheets if you wish; summary worksheets are not linked to the source data worksheets (i.e., those downloaded from the web).

Download more than one report series to the same file: After downloading a report series, return to the ‘Controls’ worksheet, identify a new report series and repeat the data retrieval steps listed above. This process will allow you to download more than one report series to a designated file.

Add report series to the dropdown menu: There may be certain report series that you return to repeatedly for download and summary that are not preloaded in the DataDude. In such cases, you can add a report series to the dropdown menu as follows:

IMPORTANT: Make the following changes in your main ‘template’ copy of the DataDude (UCB_DataDude.xls).

(a) Go to the ‘ReportList’ worksheet. The ‘ReportList’ has the following columns:

Column / Title / Purpose / Sample entry
Column A / Descriptive Name
For dropdown menu / Displays in dropdown menu on ‘Controls’ worksheet. / Child Welfare Supervised Foster Care Point-In-Time Reports by Ethnicity
Column B / Short Name
For filename / Used as part of filename for new Excel file. / Children in Foster Care by Placement Type & Ethnicity
Column C / URL (Web address)
(Ending in .html) / Used by DataDude to locate web pages for download / [See below]
Column C sample:

Users can edit data in rows 6 through 24 (not recommended). Users can enter data in rows 25 through 55.