hello and welcome to our live training
on calculating and uploading non-SSN GPAs and web grants for high
school counselors this webinar is the second in a two-part series and we’re
just going to jump right into it so in today’s agenda we’re first going to talk
about calculating GPAs we’re gonna move on to GPA tools and the file layouts
we’re gonna talk about creating and uploading the text file then we’re gonna
move on to manual GPA submission and we’re gonna finish it up with the next
steps so calculating GPAs the California Education Code mandates that all public
and charter high schools upload GPAs for their seniors by October 1st of each
year they also have to provide their students and parents with an opt-out
option no later than January 1st of the students junior year most of times we
kind of recommend this to be a passive option so instead of saying hey submit
this form if you would like us to opt you in for it you should ask them have a
form the parents would fill out and submit to you to opt out right because
most of the time these students really don’t want to opt out it really doesn’t
hurt them it’s just a requirement you have to provide them so stick with the
passive approach so then you have to verify high school graduation by August
31st of the academic year following graduation so the sunset of SSN GPA
uploads so beginning with a high school senior class of 2020 the Commission will
remove the capability for schools to upload student GPAs using social
security numbers and web grants although we will continue to offer ongoing
training for the non SSN GPA upload method so schools should insure their
student information systems such as Aeries power school illuminate Infinite Campus
have the capabilities to produce GPA files and text that’s .txt format
without SSNs we really do suggest that you contact your vendor directly as we
have no expertise of these systems the Commission will offer live webinar
training on a continuous basis to assist schools that are new to this process or
need a refresher so please check our training page on a regular basis to see
the upcoming webinars so for the academic year checklist and September
it’s recommended that you provide demographic verification forms to
seniors in preparation for the upload in October – starting October 1st that is the
deadline to upload the 2020 21 GPAs for your current seniors and prior year
seniors so between October and March really suggest you outreach weekly to
students with unmatched GPAs and missing or incomplete financial aid applications
we want to make sure that they’re matching in the system and that they
submitted an application and then we really recommend that you edit and match
GPAs weekly within your web grants account in January – January 1st you
should have already provided the GPA opt-out form to the juniors and their
parents so that if they chose to opt out they could have done so already in March
second it’s the last chance for the final GPA uploads to be entered into our
system so mainly we’re talking about transfer students and students who had
to do make up credits in May the 21-22 GPA upload functionality opens and then
in August specifically August 31st is the deadline
to verify high school graduation now the students can do this but a lot of times
they either miss this requirement in their web grants for students account so we
really would encourage all sites to do it for their students if they’re able to
do so within a web grants so we’re gonna take a second to talk about entitlement
versus a competitive awards entitlement awards are guaranteed for students who
meet financial and merit requirements there are unlimited amount of Awards
which means that if the student meets the financial and Merit requirements
they will get one of these awards the deadline to apply is March 2nd for both
the application being the FAFSA or DREAM Act application and the GPA upload
deadline is the same there is one exception and that is for the Cal Grant
B for foster youth students if they’re filling out an application and they’re
going to a California Community College their deadline is actually September 2nd
and they’d be considered for four additional years now the first
entitlement is the high school entitlement which is for seniors and
prior your grads this is going to stay with that traditional GPA which is going
to be for a Cal Grant A a 3.0 and a Cal Grant B a 2.0 that’s the merit
requirement and then for transfer entitlement this is for students who
transfer directly from a California Community College to a
four year University and they are also under the age of 28 by December 31st of
the award year this merit requirement for A and B is a 2.4 GPA students
enrolled in the California Community College bachelor program are also
eligible for the entitlement 2 award and then there’s a competitive cycle
which is much different there are three hundred thousand plus students applying
for the competitive award and this pretty much encompasses everyone who is
not considered for a high school entitlement or transfer entitlement
award there are only forty-one thousand Awards offered annually and it’s really
important to know that this is not great odds for the students so if they’re
eligible to be considered for entitlement you should really make sure
that they’re filling out their applications in a timely fashion but
something that’s new this year that’s actually quite exciting is that students
with AB 540 status which are really your dreamer students are now eligible
for competitive Awards we really recommend that you upload GPAs for any
of the following populations the first one being current and prior year seniors
so the current and prior year grads have two years to qualify for the high school
entitlement Cal Grant which is that first one we’re talking about each year
upload GPAs for both of them both the seniors and the prior graduates in two
separate batches by October 1st right we don’t let them mix into each other so
any student who transferred to your school after GPAs were uploaded you know
please make sure that you’re inputting them don’t do a whole new upload just
add them into your web grants account as individual file inputs and any other
senior or prior your grad who is not captured the initial upload we know that
sometimes a student slips through so we highly recommend that you ensure that
all the students GPAs are uploaded into web grants and then the last one any
former student who submits a GPA verification form sometimes the students
a few years out and they’ll request out of you please just do so and please try
to do in like a timely fashion if you can so for the different grades and how
you calculate them this is how it’s going to go for current seniors you’re
gonna be talking about 10th grade and 11th grade and the summers following
those grades as part of their GPA calculation for your last year graduates
it’s the same thing 10th 11th but you also get to
include 12th year grades and the summer after 12th grade as well and then for a
graduating junior you’re talking about tenth grade and the summer after tenth
grade we’re not including first year or ninth grade GPA calculations into the
mix so for the high school GPA calculation please use the unweighted
4.0 GPA scale please include failing grades unless they’re retaken and passed
if they retake it in their senior year you can include that as part of the one
year out upload however you cannot include that and the current upload if
they are currently a senior this excludes grades from PE ROTC and
remedial courses do not submit duplicate or multiple GPAs it skews GPA reports
and Race to Submit results it also is really hard to navigate through so we really
recommend that you do not submit duplicates just add additional ones in
for any one that you might have missed and you can input them right into the
system directly and then for the GPA calculation instructions please just go
to this URL here and you will have all the GPA calculation instructions that
you need so for the GPA upload window while October 1st is the deadline for
high school GPAs the upload functionality opens in May of every year
so if you’re able to get it in earlier we highly suggest you do it kind of gets
you some wiggle room to work with any mistakes or hardships you may come across so the Commission urges all high schools
to upload early and for dually enrolled students high school GPAs for students
who are dually enrolled in high school and a community college should be
uploaded before Community College uploads the GPAs usually close to the
start of a fall term high school GPAs not California community college GPAs
should be matched to high school entitlement applicants right because we
want these students be ran as an entitlement student which has to be
matched to a high school GPA so what about students who do not have a GPA
students not schools can submit proof of passing a high school equivalency test
such as the GED SAT ACT high school equivalency test which is referred to as
the HiSET and the test assessing secondary completion TASC test scores instead
of a GPA if the school is not accredited if the school grades were based on pass
or fail or the student is home-schooled students cannot use test scores in lieu
of GPA so if a student has a GPA then a student must use their GPA the
Commission will convert those test scores into a 4.0 GPA scale so please
keep that in mind so for last names and GPAs how can you help we highly
recommend that you emphasize the importance of using the same naming
conventions consistently across everything they’re going to be using to
apply for college right so this going to be the high school records and
transcripts which are gonna be mainly for the GPA uploads the California dream
application and the FAFSA and the college applications we highly recommend
that you collect demographic information forms from seniors prior to uploading
GPAs and you instruct them to use the same name they provide you with
consistently with both their college application in their financial aid
application and this is a big ask for the schools but we ask you to review non
SSN GPA status reports weekly and resolve unmatched GPAs because we really
need a student’s application to match to a GPA in order for a Cal Grant or a
different award to be processed so resolving unmatched GPAs is a campus
responsibility so the Race to Submit data is calculated from a number of matched
GPAs compared to the senior enrollment the dashboard will initially display
senior enrollment for the previous year the winners of the race to submit will
be based off of current year senior enrollment as soon as the California
Department of Education provides the Commission with updated numbers more
recently that’s been around March or April
so for best practice high school districts and campuses should try to
assign at least two staff for GPA uploads and GPA reporting and matching
it is a good practice for high schools to verify the name address the date of
birth of students prior to uploading GPAs this will help ensure accuracy of
information and reduce the number of manual GPA matches needed post upload
GPA upload user guides the demographic verification form and GPA calculation
instructions can be found on the CSAC homepage at the link listed above
another resource we have is a demographic verification form we highly
suggest that you provide your high school seniors with this form so that
they can list the last name the first name the date of birth and the mailing
address are going to be using on their college application such as their FAFSA the California Dream Act application and the different colleges
they may be applying to so next we’re moving on to the GPA tools and the file
layout so preparing for the GPA upload GPAs are uploaded via text files right
that’s the .txt some student information systems such as Aeries
produce an upload ready GPA text file if your system does not support this
functionality prior to uploading please refer to the non SSN GPA user guide
for proper formatting import your GPA data using the commission’s non SSN
GPA excel template and convert the excel template to a text file please note that
this is a technical process and we highly encourage you to work with your
IT department for support so this is very important the non SSN GPA template
requires data to be entered into a very specific format if even one field is
formatted incorrectly your upload attempt will fail therefore you will
want to reference the non SSN GPA upload user guide for the correct file layout
specifications you will likely need to modify and or reformat some of the Excel
data that you extract from your student information system to look like this
layout let’s go over the various fields of the file layout we can see that some
of the fields on the Excel template are required while other fields like the
spring school code are optional be sure to also pay close attention to the
acceptable characters column and the maximum field length listed in the
length column for example notice that the last name field allows a maximum of
19 letters so you may need to shorten this field for students with two last
names if it exceeds 19 characters please also format cells as text so Excel
recognizes leading zeros for example school code GPA
if lower than 1.0 and potentially school and/or state student IDs emails and zip
codes also notice that the graduation date and student date of birth fields
required the date to be entered in the year year year year month month day day
format a four-digit year two-digit month and two-digit day if the dates are
entered in any other format the file will be rejected
similarly the maximum length allowed for the last name field is 19 characters so
if the number of characters entered exceeds the maximum the file will also
be rejected you may encounter this with hyphenated and or dual last names please
be very mindful of these requirements will show you the Excel template in the
next couple of slides so now we’re going to be moving on to creating the text
file non SSN GPAs are uploaded via the Commission’s web grant system there
are two methods of upload a batch upload ideal for large student population or
individual manual GPA entries when keying just a small number of GPAs we will walk
you through both upload methods but we’ll start with a batch upload process
since that is the most common for batch uploads GPAs must be saved as a text .txt file before uploading to web grants some high school students information
management systems such as Aeries may allow you to automatically save your GPAs
as a text file thus bypassing the need to use the template if your student
information management system does not allow you to extract your GPAs into a
text file it will be necessary to first import the GPA data into the
commission’s macro enabled excel template the template then uses built in
macros to convert into a text file to make it upload ready to use the template
you will first need to enable editing you can also rename the file now let’s
convert your excel data into a text file this is a condensed screenshot of what
your Excel spreadsheet will look like once it’s been populated with data you
are now ready to save this as a text file right-click on the school code
column to format cells and format cells as text
you should also double-check formatting once you have confirmed that your
spreadsheet is formatted correctly you are ready to run the macro that is
embedded within the excel template running the macro will convert your
spreadsheet data to a text file click the View tab to see the macros option
then click select view macros this will open the macro wizard macro enabled
means that there is a mini program built into the spreadsheet the macros format
the data to help ensure that the text file meets the specification for upload
acceptance for example the macros will autofill with hidden flags in areas
where optional data is not provided like the students middle initial and column
J the students middle initial is not required data so if the field is left
blank the macros will autofill that field with a blank space to ensure that
the data in that row will still align correctly please note if you are copying
and pasting data directly into Excel vs. importing it with the template please be
particularly mindful of formatting extra characters including spaces often caused
misalignment of rows and columns and will cause the upload to fail selecting
the macros button opens the macro dialog box choose start data tools from the
macro name option you will need to scroll toward the bottom of the list to
find this then select run because we want to run the macros so by running
start data tools brings up this purple macro wizard the first box will appear
for a few seconds followed by the box to the right choose the save as text file
tab then use the drop-down to select the default name of blank data entry for
your GPA file make sure to save the text file to a desired location on your
computer preferably to the same location where the Excel version of the data was
saved once saved to your computer you may change the name from blank data
entry to another name of your choosing and if you get to this point that’s
great you have successfully saved your text file your text file may look similar to
this although the exact view may differ depending on the type of text file
program you use as you can see all the data lines up nicely
you are now ready to upload your non SSN GPA text file into web grants so
now we’re going to be moving on to the testing and uploading of the text file
users must obtain web grants access prior to uploading so please see your
campus web grant system administrator to request access so now that you formatted
your Excel spreadsheet let’s test it out and upload it to begin from the portal
menu select web grants then move on to non SSN GPA and finally upload nan SSN
GPA choose your file from its save location select the correct academic
year in your school segment for example high school college or Community College
and select begin upload please note selecting begin upload does not actually
upload the file it actually first tests the file for any invalid records as a
fail-safe remember if even one invalid record is found web grants will not
allow you to upload therefore if you have invalid records you may need to
clean it up and retest your file multiple times until all invalid records
are resolved if there are any invalid records in your file the non SSN GPA
file upload status will tell you how many and which records are invalid
notice that while both the number of total records and valid records is shown
the valid record will not be accepted if any one invalid record remains in the
batch make sure to click view invalid records to identify the invalid
records in this example record numbers 1 2 and 9 are invalid notice the reject
reasons such as invalid record length and invalid zip code and invalid GPA
please note you will find it much easier to correct invalid records within your
excel file versus a text file after fixing the incorrect fields on your
Excel spreadsheet rerun the macro to save the corrected data as a text file
once again then go through the same upload process to test your connected
file until 0 invalid records are returned if your file contains multiple
types of errors you may need to clean up resave and retest your text files a few
times to resolve all invalid records so you’ve cleaned up your errors on your
Excel template and you begin upload to retest the file you can see that the
non SSN GPA a file upload status window now displays
zero invalid records as a note users often think that the invalid record zero
pop-up is an upload confirmation so don’t forget to submit records to
complete the upload process upon successful batch upload web grants will
display a pop-up confirmation of the upload including the batch number before
exiting the screen you may print a confirmation report of your upload you
will also receive a confirmation via email unless your email system has a
firewall that blocks emails sent from CSAC alternately 24 hours after upload
you can retrieve your GPA upload report by going to web grants clicking on data
transfer and report downloads then you will see the non SSN GPA school
upload reports so now we’re going to be moving into the manual GPA submission
this is going to be for all the additional students that were in that
initial batch when using the add non SSN GPA screen be aware of the impact to the
award cycles pay attention to any on-screen messages this message
indicates that the GPA was being added after the March 2nd deadline only GPA
submitted by March 2nd are applied to the high school and transfer entitlement
award cycles GPA submitted after March 2nd are applied to the September 2nd
competitive award cycle all fields with a red asterisk are required however
adding optional data will improve your chances for a match when manually adding
a GPA after March 2nd it will be applied to the September 2nd competitive award
cycle the September 2nd cycle is for community college students who submit a
FAFSA or California Dream Act application and finally we’re gonna go
over some next steps so you made it this far you’ve uploaded your GPAs but your
job is not done what’s next for students who do not self certify their high
school grad date on web grants for students you must verify on their behalf
by August 31st starting October 1st you need to begin the process of linking
unmatched GPAs it is the high school’s responsibility to link unmatched GPAs to
the correct financial aid application students
with unmatched GPAs will never be awarded it is important to work reports and edit
and/or mat GPAs weekly do not wait until the March 2nd deadline because if you
need help from school support the phone lines are usually very busy and wait
times to speak to a CSAC representative are very long students with unmatched
GPAs will not be awarded a financial aid application without a linked GPA will not
be processed and it sits in pending status indefinitely this is because the
linked GPA is what triggers web grants to process a FAFSA or California Dream
Act application so remember a FAFSA or a California grant application plus a
match GPA is what puts a student in qualification for Cal Grant and finally
were going to talk about some resources just real quick to sign up for our listserv
and receive operation memos and special alerts directly to your email navigate
to our home page and scroll all the way to the bottom of the screen the
subscribe to CSAC important updates option by clicking here you will select
the listservs you can sign up for only the ones that you check are the ones
that will be sent your way so after putting your name in your email address
in and selecting the listservs you would like to receive notifications about just
enter in the CAPTCHA and submit the Commission offers many types of training
including recorded and live webinars and the annual high school counselor
workshop series now refered to as a statewide financial aid workshop series navigate to the CSAC.CA.gov webpage
choose for schools and counselors trainings and choose either the recorded
webinars or register for a live one thank you for watching this webinar and
if you have any questions please give a call to our institutional support unit
at eight eight eight two nine four zero one five three or by emailing them at
[email protected]