An interesting feature of Moodle is the possibility to create so-called “calculated” questions. As the name suggests, these are questions which answer is supposed to be numerical, and calculated. But instead of creating the question with hardcoded values, you use placeholders for variables and give the answer as a formula. You then access a rather rich (and a bit complex) set of forms to fill-in in order to create sets of values for the variables. Each set of value will result in a (slightly) different question. The advantage here is that students have a low probability of seeing the same exact question twice, thus forcing them to do the calculation any time they take the quiz — or rather, to focus on the method rather than on the numerical result.

So far so good, but the formula yielding the correct answer should be written in some kind of a PHP idiom (as Moodle is written in PHP), with the variables between curly braces. The set of operators and functions available is rather limited (for obvious security reasons, otherwise nothing would prevent you to use this subset of PHP to access and modify the underlying database: much fun incoming…). The result is convoluted at best (I personally think that “ugly” is more to the point here), as one can imagine. For example, to express the formula:

\begin{equation*} \frac{\textrm{flow}}{(1 + \textrm{rate}/100)^\textrm{years}} \end{equation*}

one would have to write:

({flow}/pow(1+{rate}/100,{years}))

No comment.

Still, I was excited with the possibilities of the system and used it to generate hundreds of simple questions about time value of money, for example. But when I started to use it for other topics, I quickly reached its limits: how to use this system for questions about Value at Risk or options valuations? These need access to (rather common) probability functions such as the standard normal cumulative distribution function, which were simply not available in the subset of PHP used in the questions (nor in standard PHP, as far as I know).

Long story short, after searching a bit in the deep space of wild bytes known as the internet, I found a nice little hack was possible: if Moodle cannot calculate the answer for you, then (pre)calculate it yourself with whatever you want (hint: a spreadsheet), and import your questions back into Moodle. The little hack is that it is possible to link to the questions some variables which are never referenced in their text: thus you create an {answer} variable and that’s it. Well, almost.

Credit should go to Hiram Bollaert, whose “CQ4M Generator” uses this very hack and put me on the way. As far as I remember, he uses Excel and a set of macros to generate his questions in a format that Moodle would happily import. I slapped myself in the face for being so stupid I could not find such a straightforward solution myself, and started to adapt it to better fit my needs.

I don’t (and won’t) use Excel, and I did not like Hiram’s table so much, so I designed my own system. Read below if you are interested.

The workflow is quite simple:

  1. Prepare your questions in your favourite spreadsheet (yes, including Excel if you really want to use it),
  2. Export the contents of the spreadsheet in a csv file,
  3. Generate a Moodle XML question file from the csv file,
  4. Import the questions in Moodle, enjoy.

Prepare the questions in a spreadsheet

I made an example spreadsheet available on Google Drive, just click on the link (no account needed) and follow the explanations below.

The spreadsheet is split in 6 sheets, one for calculations and five for the five questions we prepare here. Only the 5 questions sheet will be exported as csv later, thus you are free to do what you want in the calculations sheet, you don’t even need one. I made one to host intermediary calculations and make the questions sheets less cluttered.

The five questions are supposed to be solved in sequence and form a problem or a big exercise. Thus, the random values should be synchronized accross the questions: once chosen, the value of a given random variable — say, the interest rate — stays the same in all the questions.

question 1

We will use the first question sheet Q1, pictured above, as a template. Your questions should be organized in the same way :

  • in row 1, you have the question title, it might be anything you like
  • the question text starts in row 2 and can extend on any number of lines. The word END alone in a row shows the end of the text
  • in the example, the question text uses html tags. This is not necessary, Moodle accepts simple text as well (see Moodle doc for details about text formatting)
  • notice that the variables are indeed marked with curly braces: in question 1 there are three variables: {pv}, {y} and {r}
  • an empty row follows, to separate the end of the question from the data. This empty row is compulsory as it is expected by the filter which will produce the XML
  • then we have a row with four columns labels: there should be one column for each variable, and one for the answer. The answer column should be the last one. The labels should be the variables names, without the curly braces. Give a unique name to the answer variable: in the example, the answer is named {a} as it is an annuity.
  • under the labels come 4 rows with characteristics of the variables: minimum and maximum value for randomizing, number of decimal places, and a placeholder for the random formula which will generate the variable. Note that for the answer, only the number of decimal places is really necessary
  • finally come the sets of random values for the variables, and the associated answer. Here you notice that we have values and not formulas in the cells: we had formulas initially but then used the “copy and paste values only” menu item to keep only the values. This is necessary, otherwise the random variables will be regenerated any time you load the sheet and you will lose synchronicity of variables accross questions, which is necessary here as the questions are not independent and form an exercise
  • I limited the example to 10 sets of values (rows 12 to 21) but of course I normally use many more (usually 200). Beware that the XML file size grows fast with the number of sets, and there might be an upload file size limit on the Moodle server.

About the random variables generation, you can do whatever you want. On the image above is shown the formula I used for the random value of pv:

=RANDBETWEEN(A8,A9)*1000

I chose a random value between the minimum (in A8) and the maximum (in A9) value and multiply it by 1000 as I want a random number between 200,000 and 800,000 in thousands. Explore the other cells and the other sheets to see more examples. The rate formula (in C11) for example, shows how to get a random value with 2 decimal places. Again, there are many other ways (and random functions), allowing you to use uniform or other distributions etc.

A useful trick is to force the rounding of the answer value to the required number of decimal places, using the ROUND() function of the spreadsheet. This is a good idea because if you simply display the required number of decimal places in the spreadsheet, internally the value might be (slightly) different, and when exporting to csv, the internal value, not the displayed one, will be exported.

Export the spreadsheet as text (csv)

Once you finished preparing everything, test the questions! Double check the calculations, make sure it works as expected. Then, export/save as text or csv or whatever your spreadsheet calls it all the question sheets in a single file with the following parameters:

  • Separator: Tab,
  • Quoting: Never,
  • Character encoding: Unicode (UTF-8),
  • locale: United States/English (C).

(Yes, it’s more tsv than csv, actually).

If your spreadsheet does not allow to export all the question sheets together, export them separately and concatenate all the files afterwards. You can do this with the cat command or equivalent. Anyway, the csv files are pure text files, you can even open and edit those with any text editor (take care not to change anything unless you really know what your are doing, though).

Generate a Moodle XML file from the csv file

We now have a csv file with the questions and the variable sets, but unfortunately, Moodle does not know how to import this simple format. Thus, we have to use a filter, that is, a program that takes a text file as an input (the csv file), does various transformations on its contents, and outputs another text file (the Moodle XML file Moodle knows how to import).

I wrote one such filter some time ago, and it is available on github there: https://github.com/jcbagneris/csv_to_moodle_xml. You should have a working installation of the python language to use it (either python 2 or 3), and lxml is a dependency. Installing python is really easy, even on Windows, and internet is packed with useful resources about this. Anyway, start with the python website, that is probably all you need to make a basic installation.

Once everything is installed correctly, run:

$ python csv_to_moodle_xml.py yourfile.csv

to generate yourfile.xml with default params, or

$ python csv_to_moodle_xml.py --help

for options and parameters.

Note that with the example we used above, you should pass the -s option to make sure the variables stay synchronized accross the questions.

Import the questions in Moodle

Now, connect to your account on a Moodle server, chose a course that you can edit, go to “Question bank > Import” and import the file, chosing “Moodle XML format” as the file format. I strongly suggest that you import the questions in a special “sandbox” category and create a dummy quiz to test everything is ok before using it in a real course.

That’s it, repeat at will to create more calculated questions with arbitrary complex formulas.