Extracting Grade Data from your MIS
The first step towards uploading grades or AtL data into Analytics is to extract this for the cohort from your schools’ MIS.
To help you do this, we provide links to helpful resources or report definition files for popular MIS’. These can be found in the following article - Extracting Data from your MIS - Arbor, Bromcom, Cloud School, CMIS, iSAMs & SIMS (Admin).
Each set of grades data (e.g. Targets, Mocks, Spring assessment, etc.) would need to be uploaded in a separate file.
Excel Add-In
We would recommend downloading and installing the Analytics Excel Add-In, which is an optional tool which can be used to help format your student and grades files ready to import/upload into Analytics. For more information, see the following section: Excel Add-In.
The next section of this article then explains how to use this to prepare your grades data.
Preparing Grades Data
Once you have extracted grades data from your MIS, you should run this through the Excel Add-In to ensure it is in the correct format for import into Analytics. All grades, baselines, or AtL data within Analytics must be in a 4 column list consisting of:
- Student ID
- Student Name
- Qualification/Subject
- Grade/Level/Judgement
To help you format your grades file, there are two options in the Excel Add-In. Below we have explained this step by step, or you can watch our video tutorial:
- Prepare from Broadsheet – This formats a grades broadsheet into a 4 column list. A grades broadsheet displays qualifications / subjects along the top, students down the left and grades / levels scattered across the sheet (as shown below). For more information, see the 'Prepare from Broadsheet' section below.
- Prepare from Grade List – This formats a grade list including any number of columns into a 4 column list. This is useful for grades data exported in 5 column lists (as shown below). For more information, see the 'Prepare from Grade List' section below.
If you already have Excel open but do not have a file selected, when you click any of the Prepare Grades Data options, the Excel Add-In will prompt you to open your file.
Prepare from Broadsheet
To format a grades broadsheet into the required 4 column list, click SISRA tab > ‘Prepare from Broadsheet’.
In the pop up, select the row including qualification names. Analytics will automatically attempt to populate the Student ID and Student Name columns for you. Then select (or manually type in) the first and last columns containing qualifications you wish to include in the file. Check the options selected and click ‘Prepare Data’.
When the Excel Add-In has finished formatting the file, this should be shown in a 4 column list and you should be prompted to save the file. You will then need to save this as CSV (MS DOS) format, which is the file format required in Analytics. Then, enter an appropriate file name, browse to a suitable location on your PC, and click ‘Save’.
Do NOT save your files as CSV Macintosh or Comma Delimited; save as CSV (MS DOS).
Prepare from Grade List
To format a grades list into the required 4 column list, click SISRA tab > ‘Prepare from Grade List’.
In the pop up, Analytics will automatically attempt to populate the columns for you. If the Excel Add-In is unable to do so you will need to select (or manually type in) the columns which contain your student IDs, student names, qualification names, and the grades. Check the options selected and click ‘Prepare Data’.
When the Excel Add-In has finished formatting the file, this should be shown in a 4-column list and you should be prompted to save the file. You will then need to save this as CSV (MS DOS) format, which is the file format required in Analytics. Then, enter an appropriate file name, browse to a suitable location on your PC, and click ‘Save’.
Do NOT save your files as CSV Macintosh or Comma Delimited; save as CSV (MS DOS).
Troubleshooting when Preparing Data
Warning Icons when Preparing Data
When the Excel Add-In is unable to identify columns for you when preparing student or grades data, the associated dropdown will be flagged and highlighted red.
If you select a blank column containing no data or if the column selected contains numbers when this is not expected, the Excel Add-In will highlight the appropriate dropdown yellow.
You can hover over the icons for a handy tooltip. If you’re expecting the column to be blank in your student data file, the file will still import and Analytics will simply fill the column with the default values set in CONFIG. > Student Data Profile.
Error List Explanations
When preparing data, the Excel Add-In can identify or highlight a number of issues within the file. In this case, the Excel Add-In will open a new workbook in Excel and present the list of identified issues on the right - as shown in the below screenshot. This will include a description of the problem stopping the Add-In and specific cell references. As you can see below, the Excel Add-In will also highlight the cells causing the issues with different colours indicating the severity.
Below is a list of issues that can be identified by the Excel Add-In:
- If the student ID doesn’t contain a number
- If the student name contains a number
- The cell exceeds maximum length in characters
- The column cell name is blank e.g. “Student Name is blank”
You can then use these prompts to return to your original file and make the appropriate changes to resolve the issues. When you have addressed the problems flagged by the Excel Add-In, you can then attempt to prepare your data again using the relevant options under the SISRA tab.
Do I have to use the Excel Add-In to format my data?
The Excel Add-In was designed as an additional tool to help achieve the correct format for importing data into Analytics, however this does not necessarily need to be used to ensure data is in the appropriate format. If you do not have access to the Excel Add-In, you can manually prepare your student or grades data, by highlighting and moving columns around to ensure they are in the correct order.
-
Student Data - For any student data files, you will need to include the same columns (and they must be in the same order) as the example shown in your Student Data Profile (in Analytics, go to CONFIG. > Student Data > Student Data Profile).
In addition, each student would need to have an independent row for each subject they take. The SUBJECT column defines the ‘set of classes’, whilst the CLASS column would define the name of the ‘class’ within each set (and pulls through to the reports). All of the other columns for a particular student would need to have a unique value showing in each row, otherwise filter conflicts would occur when importing to Analytics.
- Grades Data - When setting up grade files in Excel, these would need to be in a 4 column list including: Student ID, Student Name, Qualification/Subject, and Grade/Level. Again, each student would need to have a separate row within the file for each qualification they are taking.
Both types of files would need to be saved in CSV (MS-DOS) format before importing into Analytics (NOT CSV Macintosh or Comma Delimited).
Thanks for reading!