Automated progress report of students in modules

[stelpstra]

Attached Excel workbook is able to download data from Canvas (using Get API calls) to create a progress report of all students in a course with modules to which requirements are added (see How do I add requirements to a module?).

(no data means not yet downloaded while download is in progress)

Compatibility

• MS Office 2010 on MS Windows (higher versions work most likely, but are not tested)
• MS Office 2011 on Mac OS X (version 2016 for Mac barely supports VBA and is therefore not supported)

Enable editing and content

In case Excel displays the following warnings, enable editing and content for the Workbook to work properly:

Settings

First fill in the settings:

• the Canvas url of your institution (e.g. https://yourinstitution.instructure.com)
• an API access token (check How do I obtain an API access token for an account?)
  
• select the desired content for column A and column B
• click Save

Run progress report

To run a progress report:

• find the course ID of the course from which the report needs to be generated in the address bar
  
• fill in the Course ID in the worksheet at cell B2 (e.g. 210)
  
• click the button Download data from Canvas to create the progress report

Depending on the amount of students and module items this may take a some time (about half a second per student).

In the top-left corner the download progress is displayed in percentages.

To cancel the download progress click the button Download data from Canvas again (only while download progress is less than 100%, otherwise downloading would start again).

Versions history:

• 1.11 (July 29, 2020)
  • Updated the processing of API pagination (Instructure changed the method regarding the enrolments API) to accommodate for both the old and the new method.
  • Changed some Excel formulas to prevent #DIV/0! from being displayed
• 1.13 (November 9, 2022)
  • Added a row "Requirement" below the module item names
  • If a module item has no requirement, the completion information per student stays empty and a comment is added for explanation
    (note: When no requirements are configured for a module, Canvas assumes progress as Complete, whether students interacted with the content or not at all)
• 1.14 (July 16, 2024)
  • Updated code related to pagination to accommodate for the change as indicated at https://community.canvaslms.com/t5/The-Product-Blog/Forthcoming-Update-to-Canvas-API-Response-Header...

Performance

In case you experience extremely slow performance (a minute per row or so) to the point that Excel is freezing, then make sure the Windows Defender Antimalware Client version is at least 4.18.2102.4. You can check the version on your device following this guide How to Find the Microsoft Defender Version Installed in Windows 10.

Remarks

• Disclaimer: use this workbook at your own risk (I cannot be held responsible for any undesired consequences). This Excel workbook only reads from Canvas, never writes to it and should not have any negative effect on Canvas in terms of integrity or performance.
• First try it on your test environment https://yourinstitution.test.instructure.com (slow performance is expected and may cause time-out errors) before using it on production
• Check samples from reports with actual data in Canvas or in case your student(s) get accounted for their activities in modules as Canvas is the most reliable source
• Suggestion: save the workbook as a template (xltm file) after entering the settings so you can always start with a fresh and clean workbook so you will be prompted to save it. Keep in mind that if you don't save the file in a macro-enabled workbook format (xlsm), running the report again will not work any more as it requires the macro functionality to run.
• I created this workbook largely in my own spare time (for fun, to learn, to share and because I can), so huge amounts of support can't be expected
• Leave your comments in case you are using it, if it makes your work easier or more fun

Download

Download the latest version of this file from this Google Drive folder.

Do not open in Google Docs nor in MS Office online, because that will not work, just first save the file and then open it in the Microsoft Excel application.

Additional tools

• Automated report of assignments in courses 
• Automated report of group sets, groups and members in a course 
• Create course groups from sections using Excel 
• Download data to Excel using VBA and the API (workbook with code attached) 

[stelpstra]

Hi  @aeverhart ‌, at the height of row 2 you should see

The Settings window should open when you click on the button Settings.

Also see the first image in the description on this page.

[jennifer_hogan]

Is there any way or any report that would include the Membership information, most importantly the Enrollment date?  We are using this in a virtual school setting and students start date is all throughout the semester.  We are trying to track students progress and struggling not having the enrollment date on any report.  Any help would be wonderful.  Thank you!

[punnumangal]

Hi, this excel sheet is marvelous! I am using this excel sheet from last 1 year almost every day and it used to run smoothly, but now it is not running for all the courses in the production environment. However, the report is running for all courses in the test environment. I tried running it on excel 2010, 2013, 2016 in windows laptop (latest version 1.9). I have saved as a micro enabled workbook and haven't changed anything in my settings but now I'm getting an error message for all the courses (production environment) that says "there's no course associated with that course id.". I'm an admin, so I don't think it's a permissions issue. Not sure if this is because of recent changes in canvas production environment (https://community.canvaslms.com/docs/DOC-13790-canvas-production-release-notes-2017-12-09). Any solutions suggested will be of great help and I will eagerly wait for a reply. Thank you! 

 @##

[stelpstra]

Hi  @punnumangal ‌, this can be caused by e.g.:

• you don't have (enough) access rights in Canvas to that specific course (anymore), or;
• your token is not valid anymore, perhaps it has expired.
• the Canvas url under settings is not valid (anymore). You may entered the test or beta url and the course may exist in production, but not yet in test or beta

The first thing the report is trying to get from Canvas is the name of the course. In case the report is experiences trouble with the connection (Canvas url and token), it generates the error. You can try to create a new token in your profile and use that in the report.

I hope this is helpful.

[stelpstra]

Hi  @jennifer_hogan ‌, currently enrollment and membership information is not included in the report. At this moment there are no plans to add new functionality to the report due to the time and effort that would go it to that. Perhaps your CSM can advise you on how to get this type of information, e.g. through using Canvas Data.

[punnumangal]

Hi Jaap,

Just sometime back, I was able to identify the issue. In the office, our network engineer changed some proxy settings & blocked few sites. Because of which this issue came. From yesterday, I almost tried everything. In any case, never felt that issue is coming because of the office network. As soon as I connected my laptop with my personal data connection, report started running smoothly. It’s all good now and it is working fine. Thank you very much for taking out your time to reply.

Best regards,

Punit

[rtyger]

I'd like to try.  Where do we get the app?

[awilliams]

Hey  @rtyger  Welcomeback to the Community. I hope you had a good break. There is an Excel workbook file attached to the original post up at the top that you can download and then it looks like  @stelpstra ‌ has included directions for setting everything up. Let us know if you need anything else and it is great to see you again!

[jcrawford]

 @stelpstra  This is fantastic!  Thank you so much for sharing!

We have PD courses that we offer across the district for our teachers and would like to be able to see or sort module progress (completion of PD course) by school.  We have each school set up as a sub-account. Is there a way to add a column or field for the sub-account of the user? We are a district of 1000+ teachers and 17 campuses so this would be HUGE for us when tracking PD.  Thanks so much!

[stelpstra]

Hi  @jcrawford , downloading the progress can only be done per course, not per sub-account or multiple courses. If you want to work with progress data on a large scale you may be interested in using Canvas Data.

[jcrawford]

 @stelpstra  Thank you for your reply! So we actually are wanting to pull the module progress info from one course. We have teachers from multiple sub-accounts taking a common PD course under the root account. We would just like to be able to see what sub-account or school the users are associated with... When I hover over the username, I see the long and short name, sortable name, SIS ID, etc. Is there a way to add sub-account as an additional piece of information or field? Does that make sense? 

[stelpstra]

Hi  @jcrawford ‌, at this moment there are no plans to add new functionality to the report due to the time and effort that would go it to that. A workaround could be to export a provisioning report, import it in the Excel worksheet and search or filter to find the sub-account for each course. See also https://community.canvaslms.com/docs/DOC-12627 

[Boekenoogen]

Any future updates to this google sheet? I really like this tool and have been using it for sometime. 

[stelpstra]

Hi  @jrboek , I can see the added value of google sheets, but I'm not planning to rebuild it from scratch in a complete new technology (it won't work with VBA code).

[jacobtowne7]

Hi  @stelpstra ‌
Is it possible to modify a sheet like this to pull data from more than one course at a time? 

[stelpstra]

Hi  @jacobtowne7 ‌, you can copy the main worksheet for each course you want the data from and download the data one worksheet at a time. You cannot download all the sheets all at once and the Statistics worksheet will only match one sheet and will be use the same data on each page (the pie charts will be mismatching all other sheets you create). The pie charts are not created from the VBA marco code, so you can easily modify them to your wishes. If you do something like this I would recommend you create a new XLTM (macro enabled Excel template) to create a new workbook structure to use as a default. If this would be a solution to your needs, could you share here your findings? I hope this is helpful to you.

[jacobtowne7]

Thanks  @stelpstra  thanks very much for that. I also found a way to get the section sis id on the spreadsheet so that the id appears each time you download. I just have to manually add a vlookup formula (and another sheet with all the section name/sis id details) to add the section name too, unfortunately this has to be done each time you click the button. I have emailed you also to show you what has been done. 

[phillip_thouin]

We were scouring a while for an efficient way to report student progress, and found this gem!  Thank you for developing a very useful tool, and for the clear installation tutorial.  Will continue reading this groups' comments as we learn how to implement.

[bwarriner1]

In running the excel file recently I'm getting the following error

 an error occurred object variable or with block variable not set

I was curious to look at the macro but noticed the ability to edit it was greyed out and it appears password protected

[stelpstra]

Hi  @bwarriner1 ‌, sorry to hear you are running into issues. The code is protected because your token is stored in a hidden sheet which can be compromised by anyone with the password. Therefore I will not share the password.

Because this same Excel file is working properly with many users, this error is most likely caused by a specific version of Excel. Note that Excel 2016 or higher on Mac is not supporting VBA and will give you errors with this workbook. I know of just less than a handful of people using Excel 365 ProPlus (with security hardening enabled) who experience similar issues as you mention. Which version of Excel and on which OS are you running the workbook with?

[smukhopadhyay1]

Hi Jaap. This is an awesome tool you have built and it has saved us so much time and effort. Thank you so much for this. If you are planning any enhancements to this sheet, may I suggest a button to clear the sheet. 

[stelpstra]

Hi  @smukhopadhyay1 , I'm happy it is useful to you. A clear sheet macro was already present in the workbook. In version 1.10 I just attached you now have a Clear worksheet button to easily clear the worksheet.

[smukhopadhyay1]

Thank you so much!. Now its perfect!

[Boekenoogen]

I just downloaded v10 and cannot get it to work. Error - Canvas returned the message: Request Timeout. I also was looking at the other tool you just updated regarding assignments and it was not working either.

Dr. John Boekenoogen

[Boekenoogen]

Thank you, Jaap for reaching out to me. It looks like this program is now working for me. 

Dr. John Boekenoogen

[jean_claude_cag]

Hello Jaap. Thank you so much for this. It really worked. But recently. I have been trying to extract my weekly update but it seems working very slow. Is it due to the new updates of Canvas?

[stelpstra]

Hi  @jean_claude_cag , thanks for pointing this out. I just tested it on a course with 50 module items and 72 students using Office 365 on a managed laptop by my university (quite strict security setup) and it seems to takes about 4 seconds per row which is indeed very slow while earlier with the same workbook it was faster. I also ran it on the same course using Excel 2010 on my personal (home) laptop and it processed a few rows per second, significantly faster. Are you also using O365 on a managed university laptop?

[jean_claude_cag]

Im using o365 from a university account. Is it also because my students are 512 that it is slow?

[stelpstra]

I don't think so, but I'm not sure. You could ask your security department to look into this using Process Monitor - Windows Sysinternals | Microsoft Docs so see what security hardening settings make the performance so slow.

[jean_claude_cag]

Dankuwel Jaap. 

[nsonawane]

Hello Jaap,

The canvas report based Macros is an excellent report to use for monitoring the student progress

But since last one week we are getting below error. Can you help with this regard?

[madeleine_adams]

Hi  @stelpstra , I'm having a similar issue in that yesterday when running the Excel file I received the message "An error occurred. Object variable or With block variable not set". I have also tried re-creating the spreadsheet with a new API token to no avail. Earlier Course IDs seem to still work but not for those in the 3 digits (the ones I need). The same spreadsheet was working fine 2 weeks ago for all Course IDs. I am (and have been) working on Windows with Excel 2016. Any help would be great. Thanks!

[eva_bautista]

Hi Jaap Stelpstra, this has been an amazing report, thank you for sharing with the community. i've been running the report successfully up until last week. The canvas course that I have linked to the report should be getting me data for a total of 145 students, however, it seems the report is truncates where student last names starting with letter "H" and only gives me data for the first 45 students. Not sure what I can do to get the complete set of data for the 145 students. I've re-ran the report several times and even re-generated the token key, but still not getting the full report. Any ideas what I might doing wrong or what I can do to fix it? Thanks!

[stelpstra]

Hi  @eva_bautista ‌, for the duration of one or two weeks this report was not working and generated an error message that something went wrong and no student data was downloaded. This was because Instructure changed the method regarding the processing of API pagination of the enrolments API (I have not yet seen a notification this was coming). I updated the code handling the pagination for all API calls to accommodate for both the old and the new method. Version 1.11 is now available for download.

[stelpstra]

Hi  @nsonawane ‌, for the duration of one or two weeks this report was not working and generated an error message that something went wrong and no student data was downloaded. This was because Instructure changed the method regarding the processing of API pagination of the enrolments API (I have not yet seen a notification this was coming). I updated the code handling the pagination for all API calls to accommodate for both the old and the new method. Version 1.11 is now available for download.

[stelpstra]

Hi  @bwarriner1  and  @madeleine_adams ‌, for the duration of one or two weeks this report was not working and generated an error message that something went wrong and no student data was downloaded. This was because Instructure changed the method regarding the processing of API pagination of the enrolments API (I have not yet seen a notification this was coming). I updated the code handling the pagination for all API calls to accommodate for both the old and the new method. Version 1.11 is now available for download.

[AlinePorfirio]

Hello, @stelpstra I can't download the file, can you send again please?

[mrwnphs]

I really love the idea of this spreadsheet script, but it runs very slowly.  I wonder if I'm doing something wrong.  I have a course with 3 sections- just me, one teacher.  I only have about 30 students per class.   And I'm running the script from a new high powered gaming machine with a Windows desktop based Excel- can't tell what version right now because the script is running and Excel is non-responsive, but the version of Excel is recent.   I tried using each of the sections "course IDs" if that's what they actually are, but they did not do anything and I had to abort each time.   So, I went with the parent course ID and that started the process just fine.  But it crawls through the first few percent.   This is a class (3 classes) that just started yesterday, so we're only talking about 2 days worth of data.   My internet bandwidth is very good (200 up/down) and I'm doing this at 10pm at night.   I'm impressed that the data is coming down at all, that's pretty cool.  But it's so slow, I can't see using it regularly.  I guess I could let it run over night, but that seems a bit extreme for the task.   I'd appreciate any advice and I'd be willing to try any suggestions.    

[stelpstra]

@AlinePorfirio, this was a community platform issues which has been fixed by Instructure. Could you try again?

[stelpstra]

@mrwnphs, sorry to hear the report is working slow. If you are running Office 365 on a managed laptop by your institution, you may experience an ultra slow performance due to the hardening policies applied to your environment. The IT department at my university has offered me a folder with less or no hardening policies applied to be able to run the reports in a normal way (still taking between second or minutes). You could ask your IT department to check what is happening using this Microsoft process monitoring tool https://docs.microsoft.com/en-us/sysinternals/downloads/procmon . If your report is building up, even if it is going very slow, it means the report is working properly and the issue is most likely caused by security settings. If the file is in a folder synced with OneDrive, then disable the autosave button in the top left of the Excel screen, to prevent autosaving with each mutation per cell. Another solution is to use an older version of Excel. I hope this is helpful to you.

[eglover1]

@stelpstra Your spreadsheet looks like exactly what I need to keep track of several self-paced courses, however I can't get it to run. Excel usually stops responding after 1% (course name and my name appear). Occasionally, it will load 4% (course name, my name, one student name, and one student ID number will appear). The only error message I've seen is "object variable or with block variable not set", but usually it just stops without any particular error message--just Excel (Not Responding) at the top. I'm using Microsoft Office Professional Plus 2016 on a Windows 10 Home Version 2004. Any suggestions?

[mrwnphs2]

@eglover1 I suspect that it's still running, you just need to let it run longer.  I had the same issue, but I just left the thing to keep running and eventually it finished.  I recommend several things:

- make sure your computer does not go to sleep after some time period

- start the report at night and let it run overnight

- make sure you have a decent bandwidth for your internet connection and decent horsepower on your computer

[mrwnphs2]

@stelpstra I do have a feature request, if it's possible.  It would be so nice if there was a way to pull in the section that students are in for cross listed classes.  Thanks!

[stelpstra]

Hi @eglover1 and @mrwnphs2, frequently I have the same issue on my university laptop at work and I am trying to find the root cause of the extreme slow download speed. Clues about why this happens that I have found at this moment is that Windows Defender is heavily monitoring the data that is downloaded from Canvas, causing Excel to run in a loop to try to read or write the Excel file and almost freezing in the process. It is true that if you wait long enough, Excel will finish the download slowly. I found a post by Microsoft explaining that this is related to a depreciated detection, which they have already removed in 2018, but could still be in the cached detection list. Unfortunately I cannot test the suggested solution to remove the cached detection because my university laptop is managed by our central IT department, although I already have asked them to help me solve this. When I know more I will share my findings. Please do share with me (private message is fine) in case you find a way that it works at normal speed again. Sorry for the inconvenience.

@mrwnphs2, your request regarding the sections in cross listed courses would entail a complete new project, at the moment I'm not starting any new projects as I am and have been building these tools in my private time for free.

[eglover1]

@stelpstra I'm still not having any luck. I've tried leaving it on and running overnight several times. I'm consistently hitting 4% and then getting the error message "object variable or with block variable not set". After that point, nothing else happens. Its stays at 4% indefinitely. 

[JSchlicher]

@stelpstra Thank you very much for creating this! I try to download date using this report, but it won't get passed 2%, before simply crashing. I am using O365 and don't have access to a downgrade at this moment. Is there any possibility of a compatibility fix for O365, or something to that effect?

[matthewheinlein]

Ah, my apologies -- I did not search thoroughly enough through the other comments to verify that this issue has indeed been encountered by others!

Has anyone else experienced the following? When I try to run this report using Excel 365, it will take FOREVER, if it completes at all. However, if I run it using Excel 2019, it runs just fine. It's a bit annoying because our team has to have a dedicated computer set aside with Office 2019 installed, when the rest of our institution uses Office 365. Curious to hear if others have noticed this!

[stelpstra]

Dear users who experience slow performance of the repost using O365.

Windows Defender is hijacking the API connection as described in https://stackoverflow.com/questions/53213215/antivirus-false-positive-in-my-vba-excel-macro
There it is stated that a depreciated antivirus signature would be the cause of this. Also https://github.com/VBA-tools/ is mentioned and I have built the report using this framework.

The thread is showing some advise of Microsoft on how to remove the depreciated signature, but of course it is not up to me to instruct you to change your security settings. Apart from that I am not a security expert with technical knowledge of Windows Defender and I have no idea what the implications are. For now I do not know how to help you better then just to inform you of what I have found out until now.

[twassmer]

Great resource @stelpstra ! Can it also grab the student's email to generate Zoom breakoutrooms?

[matthewheinlein]

@stelpstra Happy New Year! By chance will you be resharing your related post with your automated Canvas course groups report? It looks like the old article is no longer available. We're currently using v1.3 of your Excel template -- is that the most current? Thank you very much!