How do I extract Unduplicated Pupil Counts (UPC) from PowerSchool?

What are “Unduplicated Pupils”?

According the the LCFF FAQ section of the California Department of Education web page located at http://www.cde.ca.gov/fg/aa/lc/lcfffaq.asp:

How are “unduplicated pupils” defined for purposes of calculating supplemental and concentration grant amounts? (Revised 08-July-2014)

Supplemental and concentration grant amounts are calculated based on the percentage of “unduplicated pupils” enrolled in the LEA on Census Day (first Wednesday in October). The percentage equals:

  • Unduplicated count of pupils who (1) are English learners, (2) meet income or categorical eligibility requirements for free or reduced-price meals under the National School Lunch Program, or (3) are foster youth. “Unduplicated count” means that each pupil is counted only once even if the pupil meets more than one of these criteria (EC sections 2574(b)(2) and 42238.02(b)(1)).
  • Divided by total enrollment in the LEA (EC sections 2574(b)(1) and 42238.02(b)(5)).

All pupil counts are based on Fall 1 certified enrollment reported in the CALPADS as of Census Day.

So why would we want to extract our Unduplicated Pupil Counts for any time other than Fall 1 Census Day?

Some schools, such as new and expanding charter schools, need to submit a “Charter 20 Day” report that contains Unduplicated Pupil Counts.  Depending on your district timelines, you may be required to submit UPC data before or after Census Day.  The best way to know is to check with the CDE to see if they have a specific deadline set for the collection of this data.

For the 2014-2015 school year, Unduplicated Pupil Counts (Charter 20 Day) is due October 31, 2014*

*Source:  http://www.cde.ca.gov/fg/aa/pa/aavataglance1415.asp or http://www.cde.ca.gov/fg/aa/pa/charter20dayinstr14.asp

Step 1:  Select the group of students that were enrolled on the day that you wish to retrieve your UPC.

In the example that we will use here, we assume that the 20th in-session day of school was on 9/5/2014.  We select students from this date by using the query:

*as_of=9/5/2014

Screen Shot 2014-09-25 at 7.23.11 PM

Click on “Quick Export” from the group functions menu in the bottom right hand corner.

Step 2:  Quick Export the key fields needed to determine the student status.

For this analysis that we will perform in Excel, we use the following quick export code:

Student_Number
State_Studentnumber
lastfirst
Grade_Level
DOB
CA_ELAStatus
LunchStatus
CA_PrimaryResidence

We can then open the information in Excel.

Screen Shot 2014-09-25 at 7.54.51 PM

Step 3:  Decide if a student counts as FRMP/EL/Foster Eligible

In order to determine eligibility, we reference the LCFF FAQ section of the California Department of Education web page that is linked at the top of this page.

EL

Which students are classified as English learners under the LCFF? (Posted 08-Jul-2014)

A student will be classified as an English learner for LCFF purposes if he or she is identified in CALPADS as enrolled on Census Day with an English Language Acquisition Status (ELAS) of “English learner” (EL).

  • Therefore the student is counted if the “CA_ELAStatus” field is English Learner (EL).

FRMP

Which students are “eligible for free or reduced-price meals” under the LCFF? (Posted 08-July-2014)

Any student who meets the federal income eligibility criteria or is deemed to be categorically eligible for FRPM under the National School Lunch Program (NSLP) will be counted as FRPM-eligible.

  • Therefore the student is counted if the “LunchStatus” field is Free(F), Free Direct-Certified (FDC) or Reduced (R).

Foster

Who are considered “foster youth” under the LCFF (Posted 08-July-2014)

Pursuant to recent revisions to EC Section 42238.01(b), the following children and youth are considered “foster youth” for purposes of the LCFF:

  • A child or youth who is the subject of a petition filed under Welfare and Institutions Code (WIC) Section 300 (meaning a court has taken jurisdiction over a child and declared the child to be a dependent of the court due to the presence or risk of abuse or neglect). This includes both children who are living at home while a dependent of the court as well as children who the court has ordered to be removed into the care, custody and control of a social worker for placement outside the home.
  • A child or youth who is the subject of a petition filed under WIC Section 602 (meaning a court has taken jurisdiction over a child and declared the child to be a ward of the court due to the child’s violation of certain criminal laws) and has been ordered by a court to be removed from home pursuant to WIC Section 727 and placed in foster care as defined by WIC Section 727.4(d).
  • A youth between ages 18 and 21 who is enrolled in high school, is a non-minor dependent under the placement responsibility of child welfare, probation, or a tribal organization participating in an agreement pursuant to WIC Section 10553.1, and is participating in a transitional living case plan.
  • Therefore the student is counted if the “CA_PrimaryResidence” field is Foster Family Home or Kinship Placement – (210)

Step 4:  Flag students that count as FRMP/EL/Foster Eligible

In column “I” of the excel file, title the column “UPC” and apply the following code to row 2 and copy down:

=IF(F2="EL",1,IF(G2="F",1,IF(G2="FDC",1,IF(G2="R",1,IF(H2="210",1,0)))))

Screen Shot 2014-09-25 at 8.02.43 PM

Step 5:  Count the number of students in Column I to find the UPC

Use the following formula to count the total in column I that were flagged as UPC.

Before:  =countif(I:I, 1)

Screen Shot 2014-09-25 at 8.05.55 PM

After:  201 is our UPC!

Screen Shot 2014-09-25 at 8.06.49 PM

Step 6:  Calculate the UPC %

Remembering that our enrollment on 9/5/2014 was 209, we use our UPC count of 201 to perform a simple % caculation.

(201/209) * 100 = 96.17% = Our UPC %!

 

Updated on April 27, 2018

Was this article helpful?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.