KS1 2010 LA MAINTAINED SCHOOLS SPREADSHEET GUIDANCE NOTES
Two alternative methods are available for the school to produce the KS1 data return file:
File Production Method A) By export of a suitable KS1 return file from school‟s MIS:
It is anticipated that schools will enter KS1 Teacher Assessment scores for pupils into school‟s
Management Information Systems (MIS) for school‟s future reference and so that the data is
included in any common transfer file export at such time as the pupil transfers to another school.
Given this data will be entered in the MIS and that some MIS systems are capable of exporting
the KS1 TA data in a suitable KS1 data return to the LA, schools may choose to use this export
from MIS method (A) to return KS1 data to the LA (by following KS1 returns to LA guidance from
their MIS support/training providers on how to generate a suitable .XML file from their MIS) and
then by using USO-FX to supply the file to the LA. Although it will be a CTF (XML) file, it must be
exported by the MIS as a KS1 TA return CTF (XML) file rather than any other sort of CTF (XML).
If producing an XML file of KS1 data from your MIS system it should be named:
211xxxx_KS1_211LLLL_001.xml (where xxxx is your school‟s 4 digit DCSF number).
This MIS exported XML file must be supplied to David Saxton via an upload to USO-FX (see
final page for further USO-FX guidance with links to guidance on how to get a USO-FX log in).
However if schools do not wish to produce the KS1 data by exporting an XML file from
their MIS system (or cannot do so or cannot get a USO-FX log in) then as an alternative
schools can use method B (the DCSF Spreadsheet Template file – see method B below).
File Production Method B) The DCSF Template will be made available from the LA Data and
Statistics Team via the School-to-School (s2s) system by the 21st May 2010 for schools to be
able to pick up during the week commencing 24th May (a week before May half-term holiday)
It will be pre-populated with all the Year 2 pupils that were returned in the 2010 Spring School
Census. Use of this DCSF Template is as an alternative to the MIS export Method A (above).
Procedure that must be followed if using the pre-populated spreadsheet supplied by LA
[Schools using the DCSF Template may use either the pre-populated spreadsheet supplied encrypted by
the LA via s2s or may download a copy of the unencrypted unpopulated template from the LGFL].
Note: The procedure below applies for either the pre-populated or unpopulated template. [Remember that
if using the unpopulated template and wishing to send the return to the LA via s2s rather than USO-FX
then schools must encrypt the unpopulated file once it is completed before uploading it to the LA via s2s]:
1. Download the template spreadsheet (using the “Save” option rather than using “Open”)
to an appropriate folder on your computer (noting it‟s location). We recommend that you
download the pre-populated file from s2s rather than using the empty one from the LGFL.
We advise keeping a copy of the template called “KS1 2010 blank backup” that you will
neither open nor work in (so that if necessary you may later make a copy of this backup
file to recover the template in it‟s original state if having template or macro problems).
2. Create a copy of the template and call this “master copy KS1 2010 xxxx 001.xls” (where
xxxx is the school‟s 4 digit DCSF number). It is in this “master” file that you will do your
work. Please note the location folder and ensure it is set to a folder that appropriate
school staff can find again for future reference throughout the rest of 2010 and beyond.
3. Close both files and by exploring appropriate folders ensure that both files can be found
where you expect them to be, so that you are certain of where they are on the computer.
Open only the file you created with the filename beginning “master copy KS1 2010… .xls
4. On opening the template, you must always “enable macros”. You do need to be asked
this as the template opens (Excel) Warning: Only once macros are enabled is it safe
for you to work in and save the template file. [When the template opens, if it does not
ask whether to enable macros, your Excel security settings may be too high: see below].
Page 1 of 8 Version 19/05/2010
After doing 1–4 above, when opening the file it will ask for a password, type in the word
'Secure' immediately followed (with no spaces) by your school’s s2s password
(note: upper case 'S' and lower case for rest of that word then the school-to-school s2s
password in it's usual mixture of Upper case / Lower case / numbers. S2s passwords
are known to the school‟s s2s users typically the Admin staff or school‟s data manager).
[If you were not asked to enable macros your Excel security settings may be too high –
To change this once the file is open, in Excel go to your Excel toolbar and click on: Tools
> Options > Security > Macro Security > Select "Medium" and close and reopen the file.
You ought to be asked if you wish to enable the macros every time the file is opened].
Important: If you did not enable macros then you must do so before saving any work – if
you save the sheet without enabling the macros you are likely to break the sheet‟s ability
to validate and export your data (in the required restructured) format. If saved with the
macros disabled, you may have to abandon work and start again with a new template.
The template performs minor validation during data entry however as detailed in the final
steps of this procedure it is at the end of the numbered process below that the
“export” button (in the sheet near row 17) will be (later) used to perform the more
complex validation necessary once all results are loaded (do not push the button yet).
Later (at the end of the numbered procedure below) you will use that „export‟ to find any
problems with the data (but this will only work then if the macros have remained enabled
throughout your school‟s work in this file).
6. The spreadsheet can be used to enter Teacher Assessment (TA) and P Scale levels,
and you should add any pupils missing (e.g. including pupils who have joined the school
before the first day of Half Term 6 or any pupils reaching the end of KS1 this year but
who were not in year group 2 in January). They should be added in unused rows below
all those rows that may have been supplied with pre-populated pupil details from the LA.
For advice on pupils joining/leaving school in current academic year (responsibilities
of formerly-attended school / current school) refer to KS1 2010 ARA section 8.4.
For more advice the ARA guidance 2010 (with sections 4 onwards being relevant to Key
Stage 1) is at: http://www.qcda.gov.uk/resources/assets/2010_Key_stage_1_ARA.pdf.
EAL pupils working towards level 1 must not be given P scales unless they have SEN
as well. [refer to sections 4.6, 5.4 and 5.5 of ARA guidance (above) for advice in the
case of EAL pupils]. The template has a value of „EAL‟ in P Scales for appropriate use.
7. The spreadsheet can be used for up to 150 children.
8. It is recommended you set your keyboard to upper case before making entries as some
fields are case sensitive. However, as P Scale results are a combination of upper and
lower case, we recommend you use the drop down menus to choose the Pscale results.
9. The keyboard tab key ( →| ) will move you automatically to next box/row to be completed.
Do not paste results data into the sheet (see the guidance below after this process) as
doing this will prevent the validation working and the LA will follow up the data problems
common with such returns by requesting another return in a new copy of the template.
Do not try to sort the sheet and there is no need for the children to be alphabetical
(further guidance is below after this process for more advice on completing pupil details).
Do not type any commas in within any of the data (i.e. school name / pupil names)
(as having commas present in the values loaded will adversely affect data transmission).
Page 2 of 8 Version 19/05/2010
You may need to use the View Zoom option to reduce the print size in order to fit more
individual pupil rows and columns on screen (or to increase the print size to if you need
to see the details more clearly by making them larger on screen).
10. SAVING (approved method):
If you wish to close the spreadsheet at any time and save the data you have entered,
then click on the row 14 “Save” button (shown in the sheet itself in or near row 14)
which should ask you for the location you wish to save the file to. Do not save the file
in any other way. If you are not asked for the location to save to then a likely cause is
your macros may not be enabled (please see the warnings in paragraphs 4 & 5 above
before saving the template in this case). Do not change the file type dropdown here.
Important: Do not save file if macros were not enabled each time file was opened.
Do not save the template file once the template file has been restructured – i.e. if
surnames are in column D rather than column A (this restructuring is caused later by
the export process that saves the template file before it restructures it for the csv export).
Note the location of the file and save the sheet. It is recommended you “save” the
spreadsheet to a new filename rather than the current sheet’s name each time you
save it (by adding or editing a triplet of digits at the end of the file name) so that the
filename is “master copy KS1 2010 xxxx 001.xls” (where xxxx is school 4 digit DCSF
number; and you change „001‟ to „002‟ etc each time the file is saved using the button).
[Wherever saving the sheet, keep the encryption password already set in the DCSF pre-
populated spreadsheet Template (xls) file as sent to schools by the LA: this is needed].
Please ensure that you do not overwrite the blank backup file (from step 1 above).
11. Re-open the sheet to continue to make entries/amendments as necessary - always
select the “enable macros” option on opening. You may use the row 14 “save” button as
often as necessary while the macros are enabled.
Be aware throughout your work in the template of the export functionality available:
(a) (at the end of the numbered procedure below) you will use the row 17 „export‟ button
process to find problems with the loaded data (and this will only work then if macros
have remained enabled throughout your school‟s work in this file).
(b) (At the end of the numbered procedure below) the „export‟ process that you will run
will also save your file and if there are no problems, then the process will ask you
“if ready to export” (until all pupils and results are completed including P Scales
you should always press cancel at that prompt but you can use this process up
to that point to validate your work, as long as whenever it asks “if ready to export”
you pick „cancel‟ in answer to this message each time until all work is completed).
(c) When (in step 13 below) the „export‟ button is used - if the process successfully
validates the data then you need to understand what should automatically happen if
you choose to allow the process to export a csv file (do not use the button until all
pupil details, results and P Scales have all been completed for each row). It will
first save the template file and then irrevocably restructure it (restructuring includes
moving surnames in to column D from Column A) and then it creates a csv export file
from that restructured data. Beware that this restructuring of the template file is only
intended to occur as a temporary interim part of creating the csv file export and as this
is irreversible the template file should close itself without saving after the csv is made.
WARNING: the template spreadsheet (xls file) itself should never be saved after
the export has restructured the file (i.e. if surnames are in column D rather than A)
without it first being closed without saving. Saving the restructured template will
stop that template file from being able to be used (e.g. for validation amendments).
(however at step 12 below, the csv file can be safely saved during export process).
Page 3 of 8 Version 19/05/2010
12. When you are satisfied that all the children in your school assessed at KS1 have been
included and all result entries are complete, it is recommended you make a back-up copy
of the final results by clicking on the row 14 “Save" button (seen in sheet near row 14)
(as at paragraph 10) calling this file “master copy KS1 2010 xxxx ready.xls” (where xxxx
is the 4 digit school DCSF number).
When you have saved this data as a latest back-up copy of the spreadsheet with the file
name “master copy KS1 2010 xxxx ready.xls”, click on the same save button again to
create another copy of the file (note it’s location) calling it:
“211xxxx_211LLLL_KS1-2010sheet.xls” (where xxxx is the 4 digit school DCSF number).
13. Click on the “Export” button (near row 17) and carefully follow the on-screen instructions;
entering any missing details as directed.
Please note that any subject recorded with a level of „W‟ requires a corresponding and
appropriate P Scale recorded and equally any P Scale recorded requires a Level of „W‟ –
if this is not done, the export button gives an error showing the first affected row number
for example a problem of this type in row 24 gives the following error:
…explaining that row 24 has the first error. Fixing problems and saving (using the Button
on row 14) and then rerunning export will allow schools to find and fix each problem.
14. When all necessary data are present, (and steps 11 onwards above have been repeated
as necessary to validate the data) pressing the “Export” button (near row 17) again may
warn you about any Temporary UPNs (refer to the Temporary UPNs section of this
document). Once the data has been validated the sheet should give the following box:
Getting this box shows that the data has passed file validation stage successfully.
Choose „Cancel‟ (if not wishing to export the csv file yet e.g. if there is more data to load),
or choose „OK‟ if ready to restructure the file (as above) and you get the following box:
Page 4 of 8 Version 19/05/2010
This box allows you to create an export CSV file called “KS1_xxxx_10.CSV” (where xxxx
is the 4 digit school DCSF number you have already entered in your sheet).
Please create this csv file (noting it’s location). Please do not change the file type
of CSV, nor re-name it otherwise the LA will be unable to process it.
After you have saved this csv file, please do not amend the contents of the csv file
directly (nor save it, nor open it, nor rename it) otherwise the LA cannot process it.
Warning: If the restructured template data sheet (xls file) does not close itself, please
close the modified spreadsheet (that shows the surname in column D) without saving it.
[If a runtime error or debug error occurs at this point, it is usually because the folder path
that you are saving the file to may be too long. If this occurs answer the debug message
with “End” or answer the runtime error with “ok”. Close the file without saving it then
reopen it and repeat steps as necessary (e.g. steps 11 onwards). Choose a folder path
for the file that is much shorter when exporting the csv file].
Amending data after export:
If you wish to amend any of the details for any pupils after you have created the export
CSV file, go back to your latest saved copy of the file (likely to be a file name beginning
“master copy KS1 2010 xxxx ….xls” such as “master copy KS1 2010 xxxx ready.xls) and
then follow each of the procedures above from paragraph 3.to 14 inclusive in order to
create a new export csv file to replace the older csv file that was exported previously.
15. Submission: In previous years the LA has required the csv file itself to be returned via
s2s however given the recently-introduced need for encryption when using s2s Generic
upload (and the fact that an xls file can be encrypted but a csv file cannot be encrypted
within excel) once your restructured csv export file has been produced (using the
export button – see steps 11 to 14 above) and your data sheet (xls spreadsheet file)
has therefore had it‟s data validated successfully you will need to either:
a. Use USO-FX to upload to David Saxton (in “Tower Hamlets…”) the exported,
restructured CSV file (created using export button – see steps 11 to 14 above)
that has the file name “KS1_xxxx_10.csv” (where xxxx is school 4-digit number)
[Or (if not using the Spreadsheet Template but returning a KS1 file that has been
exported directly from the school’s Management Information System) use USO-
FX to upload to David Saxton (in “Tower Hamlets…”) the CTF XML file created
by the MIS named 211xxxx_KS1_211LLLL_001.xml (where xxxx is as above)].
NOTE: Do not email nor s2s to us the CSV file nor the CTF (XML) file.
b. If unable to use USO-FX then to use the s2s Generic Upload section you must
instead upload via s2s latest template xls file that you created above which is
likely to be (and needs to be) called: 211xxxx_211LLLL_KS1-2010sheet.xls
where xxxx is 4 digit school number) only once the school has validated the
data in the template (steps 11 to 14 above) (rather than uploading the csv).
Important: If you used a pre-populated template file supplied by the LA then it
has been encrypted already for you and will not need further encryption (however
please keep the same password – do not remove the password).
However if using the unpopulated empty spreadsheet from the LGFL website
then if wishing to supply it via s2s rather than USO-FX remember the school
must encrypt their latest template xls file created above (which needs to be)
called: 211xxxx_211LLLL_KS1-2010sheet.xls before uploading to s2s, by
following the guidance on found at: http://www.lgfl.net/lgfl/leas/tower-
Page 5 of 8 Version 19/05/2010
Warning: Files that are sent without having been validated first using the
export button near row 17 to successfully create restructured csv will be
returned again for school to perform validation (necessary before LA can use it).
16. You will need to return the completed file as specified above by either of these means –
please do not email personal data.
17. The deadline for completed and validated (see steps 11 to 15 of the KS1 procedure)
files to be received by the LA via these approved methods is 24 June 2010 in order to
allow sufficient time for the necessary LA work on this data before the end of term.
18. If you have further problems or if you need further assistance, please contact either the
LA Data & Statistics Team (020 7 364 49 42) or the DSG helpdesk (01325 39 26 26).
COMPLETING THE SPREADSHEET
School identification details
You must enable macros and enter the password above each time you open the sheet, then you
may begin entering your data as per the numbered instructions above. Enter the School name,
Local Authority No and DfES School No as prompted on-screen following the instructions
above. Do not enter commas in school or pupil names otherwise the data will not load correctly
into the LA‟s central database.
Individual children’s identifying details
Check and enter appropriate identifying details (if either incorrect or not provided) using a new
row for each child assessed at the end of KS1 (these lines do not need to be in alphabetical
order). Only empty a row if a pupil is not required (under the ARA guidance) by clearing that
row‟s values cell by cell. Do not reuse an emptied row. Any extra pupils should be entered on
the rows below all pupils that were provided in the pre-populated sheet.
Do not try to paste in results as this will prevent the file validation process from working.
If you must paste pupil identifying data into the spreadsheet‟s pupil identifying data section (do
so by pasting from another Excel sheet) – you must use the "paste special - values" option.
Do not try to insert/delete or sort rows of the spreadsheet as this can damage validation.
Surname and Forename
Enter the surname by which the child is known and the first forename. Do not separate any
names by commas otherwise the CSV export file will not load correctly into the LA‟s database.
UPN (Unique Pupil Number)
Enter child‟s 13 character Unique Pupil Number (UPN). If an invalid UPN is entered, a warning
“Invalid UPN” will be displayed in column AI of the template. Please re-enter the correct UPN in
such cases. If a child does not have a permanent UPN, allocate a suitable number, referring to
“Use of Temporary UPNs” section below and to guidance on: http://www.lgfl.net/lgfl/leas/tower-
Note: If the child‟s prior educational setting was a nursery, be aware that nurseries do allocate
UPNs. If the child is known to have had no previous educational setting then a permanent UPN
allocated by the current setting (school) is appropriate. If there was a previous educational
setting then the UPN should have been transferred from that setting in a common transfer file
(ctf) for the pupil and the UPN (? and perhaps ctf ?) must be followed up with that previous
educational setting during this KS1 collection in order to provide this to the LA. If the child‟s
previous educational setting is not known or it is not known if the child had a previous
educational setting, please follow up this detail with the child‟s Parent/Carer as soon as possible
Page 6 of 8 Version 19/05/2010
and contact the previous setting during the KS1 collection period to get the appropriate UPN
from the previous educational setting. Revised UPNs should be stored in the school‟s
Management information system and superseded UPNs can be stored in the Former UPN field
in most MIS (for more on UPNs check UPN guidance link above/DSG helpdesk 01325 39 26 26)
The use of Temporary UPNs. Please be aware that:
A temporary UPN is one that ends with a letter rather than a digit. If the school returning the
data has allocated/is allocating a temporary UPN because it is believed the child may have had
a UPN at a previous educational setting please follow up with that setting during the KS1
collection period to get the appropriate UPN from the previous educational setting for that child
and record it appropriately on the schools‟ Management Information system (MIS) (as above).
Temporary UPNs returned in your KS1 data file (either allocated by your school or a previous
setting) will be followed up by the LA with the school returning them file and the school reporting
pupils with temporary UPNs will be asked to resolve each case both in the file and in their MIS to
provide the appropriate Permanent UPN to LA before the final week of this academic year.
Please ensure that the appropriate permanent UPN is established for the child as soon as
practically possible (but do not delay the return much beyond the return deadline purely
for the sake of getting a permanent UPN from another establishment first as you may
provide the UPN revision separately a few days later by calling David Saxton and either
providing these by phone or arranging an agreed filename and uploading them as this name via
an encrypted file generic upload on s2s during the Summer term).
Gender and Date of Birth
Gender should be loaded as an Upper Case letter („M‟ or „F‟). Dates of Birth use the / symbol
not a full-stop/dot to divide Date from Month and Month from Year. If the allowable date of birth
range (being 01/09/2002 to 31/08/2003 for 2010 KS1 returns) does not allow you to load a date
of birth that is appropriate then please ensure that you use format DD/MM/YY or DD/MM/YYYY
and check the child‟s correct date of birth and if the date of birth is outside the range shown here
then call DCSF DSG (01325 39 26 26) for KS1 advice and inform David Saxton of the result.
KS1 Teacher Assessment (TA) and P Scale result levels
Enter appropriate details and result levels for each child assessed at the end of KS1 by following
the numbered procedure above. Do not paste results from another file into the Template as
this will prevent entries being validated and the LA will have to follow up on the resulting
data problems with you and it is likely that the LA will have to request a new return.
KS1 Teacher Assessment - enter the level assessed for each child in the appropriate subject.
The overall subject level for Science is calculated and displayed for a pupil in the “Science SUB”
column once all 4 individual Attainment Target levels for Science for a pupil have been entered.
TA Science column heading abbreviations have the meanings as follows:
Sc1 - Scientific enquiry Sc3 - Materials and their properties
Sc2 - Life processes and living things Sc4 - Physical processes
P Scales – these are now statutory entries for a child with SEN who is Teacher Assessed as
“W” at KS1 in speaking & listening, reading, writing, maths or overall science level. If a child is
recorded as “W” in any subject for reasons other than SEN (e.g. unable to speak English), the P
Scale code “EAL” (available in the drop down menu) will need to be entered for the relevant
subjects. See 2010 KS1 ARA Sections 4.6, 5.4 & 5.5 for details about P Scales & EAL pupils.
REMOVING CHILDREN FROM THE SPREADSHEET
If you need to remove any children and associated data from the spreadsheet, do not try to
delete the row – you must clear each cell in the row separately cell by cell. It is not necessary to
move-up remaining data to fill the empty rows, nor to sort, nor to reuse the row. Please leave
that row empty and do not fill it with any other pupil‟s details. If you need to add a pupil this must
be added after (below) all those pupils that were provided to the school in the pre-populated file.
Page 7 of 8 Version 19/05/2010
CREATING AN EXPORT (restructured) CSV FILE
When you are satisfied that all eligible children are included in the spreadsheet (including those
that joined the school before Half-Term 6 began and those that left on or after first day of Half-
Term 6), you will need to create an “export” file (a process that then validates your data) by
clicking on “Export” button (see paragraphs 11 onwards above). Do not send an unvalidated
file (see steps 11 to 15 above) where the export button has not been used to create a csv
successfully as such files will need to be returned again for the school to validate first.
MISSING DATA AT EXPORT
You will not be able to create an export csv file if any school or pupil identifying data are
missing, or if any KS1 Teacher Assessment or P Scale levels are missing or
inappropriate. On-screen messages will appear if any such data are missing/inappropriate
each time you click on the “Export” button. Please enter the missing data or correct the
inappropriate levels as prompted and then save the sheet before following the instructions above
(paragraph 11-15 above) to re-create an export file.
SUBMITTING RESULTS FILE TO LA
All school returns must be made securely. (Please see steps 11 onwards of procedure above).
Please do not email returns.
Either via upload to USO-FX of the validated csv file (created by using the export process
above in steps 11-15) or MIS-exported KS1 CTF xml file (to David Saxton „Tower Hamlets…‟)
Or via upload to Teachernet School to School (S2S) website of the validated and encrypted
Template xls file (once csv has been successfully exported using steps 11-15 above).
USO-FX guidance on how to upload a file and on getting a user account for USO-FX is available
on the LGFL at: http://www.lgfl.net/lgfl/leas/tower-hamlets/accounts/THe%20Grid/research-
statistics/homepage/home/#USOFXorEncryption or from the LA STASS Team 0207 364 4942
S2S: If you cannot use USO-FX to supply an appropriate validated file to LA then, once
the file has been appropriately validated as per steps (11 to 15 above), the template xls file
you have created called 211xxxx_211LLLL_KS1-2010sheet.xls (where xxxx is school's DCSF
number) should be found (encrypted if not a pre-populated template) and uploaded to LA via
Login in with school‟s s2s username & password. (The same as your school's COLLECT ones).
Select Upload\Upload Generic File. Browse, find your saved file, and click 'Upload File' to
submit to LA (Wait for a confirmation message that your file has been successfully transferred).
Once we have downloaded your file, if you have validated it, the LA will be able to process it.
For further guidance, our LGfL page gives guidance including late breaking info:
Support contact for assistance:
MIS support on loading data / exporting a KS1 file from MIS – your MIS Support Provider.
Encryption guidance: http://www.lgfl.net/lgfl/leas/tower-hamlets/accounts/THe%20Grid/research-
statistics/homepage/home/#USOFXorEncryption (only if using s2s but not the pre-populated Template)
USO FX Support – STASS SchoolsServiceDesk@towerhamlets.gov.uk 020 7 364 40 60
For KS1 Template and s2s support: David Saxton 0207 364 4942. (Do not email returns)
Page 8 of 8 Version 19/05/2010