Python + Excel = Gradebook Happiness

I’m really trying to streamline my workflow (and reduce grade entering errors) so I thought rather than writing scores by hand on each assessment, I’d put the scores directly into a spreadsheet.  In order to hand back scores for each assessment, each student would need to have a separate Excel worksheet, which could be printed out and attached to the original assessment. I’d also need a way to get a master list of student scores that I can copy and paste directly into my main workbook.  If you’ve worked with Excel you can guess where I was headed at this point… Visual Basic for Applications!!! The school year is starting and things are getting busy so the last thing I want to do is sit down and learn another scripting language. If only, oh if only I could do everything in Python. Well, it turns out I can. It’s a package called DataNitro, and it’s free for personal use.  You can use Python to manipulate data from within an open Excel workbook.  And it’s easy to use.

“How easy is it?” I hear you asking.  Let me show you.  I need a program to go through each worksheet in the workbook, match up the name on the sheet to my master grade list and match up each learning objective to the learning objective on the master list, then copy the score to the master list.  And here is the code:

mainst = 'Sheet1'
for st in all_sheets():
   for nm in range(3,9):
      for i in range(3,9):
         for j in range(3,9):
            if Cell(st,1,3).value== Cell(mainst,nm,1).value and Cell(st,1,2).value == Cell(mainst,nm,2).value and Cell(st,i,1).value == Cell(mainst,1,j).value:
               Cell(mainst,nm,j).value = Cell(st,i,2).value

It took me about 15 minutes to get it working. I’m so delighted. I only wish I had more data in Excel that needed analyzing.

If you want to try anything fancy with Excel, and you know a little Python, I can’t recommend DataNitro highly enough.

This entry was posted in Programming, Teaching Physics and tagged , , , , , , , . Bookmark the permalink.

3 Responses to Python + Excel = Gradebook Happiness

  1. Sally Zimmerman says:

    Glad to see you are finidng easier to accomplish what you want to with the grading. Anything that makes it easier is good!

  2. Pingback: Looking Back At LOBA – Fall 2012 | Talking Physics

  3. Pingback: LOBA Gradebooks | Talking Physics

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.