Preparing early warning lists from Moodle and MyShark

As with all of my blogs, the audience for this post is a single person, me. I use blogs as notes I can consult in the future to determine how I accomplished something, and provide a place to record formulas I used to solve particular problems. In this case I needed to connect grade exports from Moodle to class exports from MyShark to produce a list of students on early warning. As the target audience is my future self, these notes are necessarily abbreviated and may be missing steps that I know intuitively. That said, this might benefit someone somewhere when dealing with related complications.

I needed advisor information for each student which is only available from an extended student data export in MyShark.

I also needed grades from the Grades: Export menu in each course. Note that the export menu link is the same except for the course ID, so link editing can jump one from one export screen to another directly. If a course has multiple sections, then Groups have to be previously set up in Moodle corresponding to each section. Then set the filter to display each section individually and export each section separately. 

The export only needs to include each and every Category total and the Course total. Category totals are listed at the end of each category, care must be taken in scrolling and selecting each category. Only the raw percentage is used.

MyShark produces an comma separated value list, Moodle produces an Excel xlsx formatted file. LibreOffice was used to open these files and then to copy and paste them into a Google Sheets file for analysis. The analysis could have been done in LibreOffice, but my workflow uses Google Sheets so that I can open files on any device of mine, anywhere. Students who have withdrawn or dropped the course were row deleted in LibreOffice prior to copying them up to Google Sheets.

Course and section were manually entered into column A. Column B checks that rows contain matching students. 

Columns C to Q contained the full MyShark output. R to AB contained Moodle Grade export data. 

Columns X to AB contained category totals and the course total. This varied by course. Note that Moodle outputs percentages in a format that spreadsheets register as text. There is a space between the number and the percent sign. The VALUE() function in LibreOffice.org can extract the numeric value as a decimal value between zero and one. LibreOffice.org parses the cells as being percentages. Google Sheets is not able to do the same with the VALUE() function. 

Column AC is manually set to be equal to the last column with values in X to AB - this varies by course. This section will also vary by the number of categories. AC cannot simply be the average of the prior columns because the categories have different total possible points and thus different weights in the overall course percentage. 

The function:

=IF(VALUE(left(X2,len(X2)-2))/100<0.7,"assignments, ","")

strips off the space and the % sign, then extracts the numeric value, divides by 100, and checks to see if the value is below 0.7. Note that the Cat0n column contents include commas and spaces as appropriate.

AH concatenates the appropriate previous columns. This function has to be edited to reflect the number of columns for each course. 

AI checks for a nonzero length in the concatenation and, if true, generates the factors under the low grade by category.

=IF(LEN(AH2)>0,"Low performance on "&AH2,"")

Columns AK to AR pull from the prior columns to generate the data in the order required for submission. AJ is reserved for future use on absences. That will require additional columns to check for row integrity and comment columns.

Column AP contains the function:

=CHOOSE(int(VALUE(left(AC2,len(AC2)-2))/10)+1,"F","F","F","F","F","F","D","C","B","A","A")

which generates a letter grade from the value in AC2. AK to AR are then copied and pasted as plain text into a separate sheet, sorted by grades, the A, B, and C rows deleted, and then the remaining Ds and Fs are sorted into the order desired. Note that the plus one in the preceding function is necessary to cope with overall below 10%. The second A handles scores of 100%. Grades could be directly exported from Moodle, but those columns then intersperse between the percentage columns and make "fill right" problematic. Hence this final kludge on top of kludges.