The DCS grading programs — working with Excel

Quick basics A fast introduction for TAs Change history   File format The "grades" program Command-line programs   Working with letter grades Getting your class list Posting marks for your students Submitting your final grades Sending grades files by mail Installing the programs Working with Excel Working with Blackboard Previous versions

The situation

Let's suppose you have TAs who like to record their marks in spreadsheets, while you prefer to use the DCS grading programs. Essentially, you use spreadsheets as a form of communication with your TAs, while you maintain the records you actually work with in DCS-format grades files. To communicate with your TAs, you need to:

That may not be your exact situation — see "Working with Blackboard" for a case that requires further elaboration — but very likely you need a subset of what's described here. For example, you might need only to be able to convert from Excel to a grades file, or the other way around, instead of converting in and re-converting out.

It would be good to talk about spreadsheets in general rather than Excel specifically, but your author would be misrepresenting the truth if he claimed broader expertise. However, as long as your non-Excel spreadsheet will let you read and write files in CSV (comma-separated values) format, you may very well be able to proceed as described here.

It's easy to convert between grades-file format and Excel format, and I'm sure you could figure out how do it by hand without needing an explanation. However, here we list the details, and also present a couple of Python programs to do some of the work for you. Feel free to modify the programs to suit your needs, and to tell everyone about your improvements.

The steps between Excel and you

  1. The marks come in, presumably in a spreadsheet with these columns:
    • student number
    • family name
    • given names
    • columns of marks

    If it's the middle of the term, the columns of marks may include earlier work that you have already recorded, as well as the marks for whatever piece of work the TAs are now reporting.

    If your course is very large and you have a head TA whose duties include collecting marks from all the other TAs, then very likely "spreadsheet" means "worksheet in the single combined spreadsheet".

    Some things to worry about:

    • What about the earlier marks that have already been entered in your grades file? If you use gcopy properly, that won't be a problem.
    • What if a TA changes an earlier mark? Gdiff helps you find those differences. You may not want to search for changes every time you handle incoming marks, but you should at least plan to keep all the spreadsheets you receive from your TAs.
  2. If you don't have one already, make a grades file GF to hold the student records for the current piece of work. All you need to have in GF is the header; in the next step, the student records are added.
  3. You have received one or more spreadsheets from your TAs. Here's what to do for each incoming spreadsheet, which we'll call "1-incoming.xls":
    • In Excel, save 1-incoming.xls as a new file "2-csv.csv" in "Comma Separated Values" format.
    • 2-csv.csv is a text file, but not a nice one. You need to convert each line to a student record in the grades-file format, and add the converted lines to the end of the grades file, GF. You can do that by hand, or use the program 
      csv2grade.py

      If you're doing it by hand, don't forget:

      • There are four spaces, not a tab, between the student number and the family name.
      • The family name and then the given names go together in a single "column".
      • There's a tab before the first mark, and between each subsequent pair of marks.
    • Excel puts a header line at the beginning of 2-csv.csv, and there may also be blank lines. If you're converting by hand, get rid of those lines before concatenating your data to GF. If you're using csv2grade.py, don't worry about those lines; the program ignores them.
    • If you're using csv2grade.py, run it now to generate grades-program formatted records: 
      csv2grade.py 2-csv.csv >> GF

      Notice that we're concatenating on the end of the file GF made in the previous step. You have to do this for all the CSV files, one from each TA. They can all just be stuck on the end of GF.

  4. Check the grades file. Start by running glint, which will point out some of the possible troubles: 
    glint GF

    If you have more than one TA, then unless you are very, very lucky, there are students who submitted work to the wrong TA. They are then in more than one TA's marks spreadsheet, and in GF they appear twice. This causes glint to complain about duplicated student numbers.

    If one of the duplicate records contains no marks, you may well want to delete it. You might also do the same for other records with no marks, though glint will not point them out for you.

    If there are double records with marks for a student, there's a problem. The cause might be that GF contains the records for previous pieces of work as well as the one currently being reported, and that the student has work submitted to more than one TA during the term. You may well want to combine the two records for that student. (Bad news: you're going to have to do this every time a new set of marks comes in, if you have your TAs consistently reporting all their marks to date.)

  5. Somewhere, you have a master grades file. Use gcopy to copy the marks from GF to the master file. If GF contains previous marks that were already in the master file, you can use gdiff to help you check whether your TAs have changed any marks since their earlier reports.

    If you want to make other changes to your grades, or use them for some other purpose, this is the time to do that.

  6. Finally, you may want to prepare a spreadsheet to send back to your TA. The major task is preparing a CSV file, which is pretty easy to do by hand, working in whatever file editor you prefer:
    • Delete the header.
    • Delete any comments on students.
    • Replace all the tabs with commas.
    • Put commas between the student numbers and student names.
    • If you think it's worth the effort, put commas between family names and given names.
    • Save as a file with a name ending ".csv".

    Alternatively, you might want to use the program 

    grade2csv.py

    It looks after everything but removing the in-line comments from student marks. Call it like this: 

    grade2csv.py gradesfile > grades-csv.csv

    If the student numbers are nine digits long in your grades file (instead of ten), you must use the -9 option: 

    grade2csv.py -9 gradesfile > grades-csv.csv
  7. You can use Excel to convert the CSV file to a spreadsheet (xls or xlsx) file, or you can just send the CSV file itself to your TA.