EMIS Training Leveraging Excel to Verify EMIS Data - PowerPoint Presentation

Slide1

EMIS Training

Leveraging Excel to Verify EMIS Data

6/9/2016

Slide2

Overview + Outline

Starting with the basics

Adding Columns / Summing Attendance

Using Conditional Formatting

Pivotal Pivot Tables

Combining files with

VLookup

Slide3

Starting with the Basics

Basic Skills

Opening the .csv files in ExcelPreparing the spreadsheetExpanding columnsFreezing panesUsing ColorSorting dataVideo Basic Skills

Practical Application

Reviewing Calendar Display CSV

Slide4

Click the Calendar Collection Review or Preview Link

Slide5

Click the Calendar Collection Review or Preview Link

Set File Format to CSVClick Generate Review DataClick Calendar Display.csvOpen in Excel

Slide6

With the file open in Excel perform these steps

Click the green triangle to select the whole worksheet – top left above row 1 and to the left of column AExpand all columns by placing cursor between column A and column B until cursor shows as a plus sign with arrows pointing left and right – double clickColumns are now expanded to show all information

Slide7

Select Row 1 by clicking the cursor on the number 1With Row 1 selected click on ViewClick on Freeze Panes drop down arrowSelect Freeze Top RowIf desired – fill row 1 with a contrasting colorClick in Column B – titled Row Number – Sort A to ZThis action brings all attendance patterns together

Slide8

Scroll down and select a set of rows for the same week. This example shows one week.

11 - 6.00

12 - 0.00 (UPDO)

13 - 4.17 (UPDO)

14 - 6.00

11 - 5.50

12 - 0.00 (UPDO)

13 - 4.50

(UPHS)

14 - 5.50

Slide9

Parent-Teacher & Professional Development

Row Number 75 and 76 – scroll all the way down in the Excel file

Professional Development hours and Parent Teacher Conference hours are found in Rows 75 and 76

Verify that the data is present and accurate

Teacher Professional Development Hours and Parent Teacher Conference Hours are not included in the calculation of Student FTEs. They do count for minimum school year requirements

.

Slide10

Quick Check

Do the total hours in each attendance pattern match what is true for your district?Are the PD and PTC hours correct?Are all exceptions present?Planned Days OffDays Shortened Unplanned days

The Calendar data is extremely important as it drives both funding and attendance percentage, plus other data checks such as mobility.

Slide11

Adding Columns & Summing Attendance

Excel skills

SortingFilteringAdding columnsUsing the Sum function

Practical Application

Attendance checks

FS Record Checks

Slide12

Attendance Reporting

Use FS from Data Collector – Preview/Review

Open in Excel

Add column between AD and AE

Label Column Total Attendance

Insert formula into new column

=AB+AC+AD

This gives a total of all attendance fields

Using Filter – limit District Relationship to 1

Filter Withdraw Reason to show just ** rows

Slide13

Attendance Reporting

Sort Total Attendance Column – smallest to largest

Look for problems

Zeroes are a problem

Less than full year could be Percent of Time or late enrollment

While you’re here – check some things for the FTE reports

Are all Percent of Time values correct – use Filter

District Relationship – 3 with >1 Percent of Time – use Filter

Effective Start Date – one day after school starts???? – use Sort

Slide14

Quick Check

Did you find students with zero attendance?Did you find percent of time issues?Can you determine how many students are open-enrolled out?

The Student Standing FS data drives funding for each student. Incorrect FS records that contain inaccurate data might not be an EMIS error – but they may cost a loss of funding.

Slide15

Using Conditional Formatting to check RIMPs

Excel Skills

Copy/PasteFill featuresSortingConditional Formatting

Practical Application

Review TGRG data between GQ & FN – Checking RIMP Codes

Slide16

Checking RIMP Codes

Use GQ from Data Collector – Preview/ReviewOpen in ExcelAdd a filterFilter to show RIMP Codes (152xxx)Uncheck 152330Add 151500 (it is a valid RIMP Code)Copy the SSIDs remainingOpen a new worksheetLabel A1 as SSIDPaste – add yellow fill to all values

Slide17

Checking RIMP Codes cont.

17

New Worksheet after paste & fill

Slide18

Checking RIMP Codes cont.

Use FN from Data Collector – Preview/ReviewOpen in ExcelAdd a filterFilter Column Q to show “NO”Copy the SSIDsMove to the New Worksheet in GQ filePaste SSIDs directly below Paste – leave FN unfilled

Slide19

Checking RIMP Codes cont.

Move to the New Worksheet in GQ filePaste FN SSIDs directly below Yellow GQNow we get to learn a cool tool

GQ

FN

Conditional Formatting

Slide20

Checking RIMP Codes cont.

Highlight Column AFind Conditional Formatting IconSelect Highlight Cell rules Select Duplicate Values

Slide21

Checking RIMP Codes cont.

Slide22

Checking RIMP Codes cont.

What are you seeing?

Lines 22 and 23 have no fill – so no duplicate found in GQ set of data

These 2 children have a NO for 3

rd

Gr

Rdg

and do NOT have a RIMP code.

Check them

Yellow lines mean GQ but not listed as NO on FN

Slide23

Checking RIMP Codes cont.

Don’t forget –

Use filters and sorting

Sort by colorFilter on Fill ColorFilter on Text (reduces scrolling)

Slide24

Quick Check

Did you find students who have a “Not on track” status and No RIMP code?Did you find RIMP codes for students who are ‘On track’? Why is this valid?What other record type could you check against the GQ RIMP Codes?

Students with a “Not on track” on the FN record should have a RIMP (Reading Improvement Monitoring Plan) program code on the GQ record. Districts who do not provide RIMP plans for students who are not on track reduce their Third Grade Reading Guarantee Score.

Slide25

Pivotal Pivot Tables

Basic Skills

Pivot Tables

Practical Application

Checking FD DataChecking FS DataOther records?

Slide26

Checking FD Data with a Pivot Table

Use FD from Data Collector – Preview/ReviewOpen in ExcelSelect the whole sheetInsert Pivot TableDrag State Student ID to ValuesDrag State Equivalent Grade Level to RowsDrag Attendance Patterns to ColumnsDrag Building IRN to Filters

Slide27

27

Slide28

28

Slide29

29

Slide30

Other FD Fields to Pivot

30

Disadvantaged

Disability

LEP

Preschool Poverty

504….etc.

Click on the total to get separate sheets as needed

Sort results

Slide31

Checking FS Data with a Pivot

Use FS from Data Collector – Preview/Review

Open in Excel

Select the whole sheet

Insert Pivot Table

Drag State Student ID to Values

Drag Sent Reason 1 to columns

Drag District of Residence to rows

Slide32

Quick Check

Did you find students who have inaccurate Attendance Patterns based on grade level?What other record types could you use for Pivot Tables?

Pivot Tables can present information in a format that makes finding inaccurate information easier.

Slide33

Using the Vlookup Function as a Check

Basic Skills

Vlookup Function

Practical Application

Comparing Longitudinal Grad Report with Current Data

Slide34

Using VLookup

Ask the experts - Use Excel HelpClick on the ? MarkSearch vlookupSelect VLOOKUP: When and how to use itWatch the video

What data do you have?

ODE report with 2016 cohort (the denominator of the district’s grad rate.)

Current 2016 grad list from your student information system

How can you tell who is on the ODE report – yet not in your grad list?

Slide35

Using Vlookup to Verify Grads

Create an Excel file from your student information system of 2016 Grads – including SSID as the first (left-most column)

Sort the sheet by SSID

Open Longitudinal Grad

Report

(2015G_LONG_GR_2016_4Y)

Filter to show the Y values on the

Denominator_Flag

(column J)

Sort the sheet by SSID (column C)

Insert a column into the worksheet as the first (left-most column)

Name the column `SSID from SIS`

Slide36

Using Vlookup to Verify Grads

With both workbooks open – click in Cell A2 of the Long Grad workbookClick on the Formulas tab at top of screen Click Insert Function Choose VlookupClick OK

Slide37

Using Vlookup to Verify Grads

Now the fun part!Lookup_value = Find What?Table_array = Where is it?Col_index_num = Which Column in table arrayRange_Lookup = False (returns only exact matches

Slide38

Using Vlookup to Verify Grads

What are we doing in English!

Please use the SSID from the Long Grad file to find a matching SSID in the

SeniorsFromSIS

file.

When you find a match, grab the student’s last name from the

SeniorsFromSIS

file and copy it to the Long Grad file.

If you don’t find an exact match on SSID, then return N/A so I know it’s not there. Thanks!

Slide39

Using Vlookup to Verify Grads

Find a match to D2 in SfS fileYou will find it here >Where will I find what you want returned to Long Grad?Range_Lookup = False (return only exact matches)

Slide40

Using Vlookup to Verify Grads

Click in A2 Double-click on + to formula downSort or Filter to find #N/AThese are the students who are in the 2016 Cohort that you are not reporting as a grad.

Slide41

Quick Check

How would you use FA and FD to find students missing required assessment records?What other record type could you compare to the FA for missing assessments?

The

Vlookup

function is a little tricky to get the hang of, but once you do, you can connect lots of information together.

Other uses for

Vlookup

is finding students without assessment records by doing a

Vlookup

between the FS and FA records.

Slide42

Let’s Review Our New Skills

Jot down a list of Excel skills you learned from this presentation.

How can you use these skills with other EMIS data sets?

Practice these

skills by:

C

reating a pivot table of your FN record.

Creating a

Vlookup

between FA and FD

Use Conditional Formatting to compare GN to FS – does everyone being educated (FS) by you have a course in the GN?