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.