User Reference

Structure of a question database

Each spreadsheet document must at least have three sheets:

  1. One sheet named settings which stores project specific settings for this question database:

    • In this sheet in the first column the tags below need to be written and in the second column the specific values for each tag.

    • It must specify categoriesSheet with the name of the sheet, which lists the Categories.

    • It must specify imgFolder which is the directory name for the pictures, relativ to the path of the spreadsheet itself.

    • It may specify any of the tags listet in Settings tag definitions Tags are listed in the first, their values in the second column.

  2. One sheet which stores the information about the different categories of questions.

    • The name of this sheet must be as specified by categoriesSheet in the settings.

    • It may/must have at least those columns:

      categorymandatory

      in this column all the different category identifiers are listet. Those identifiers must be coherent to the names of the sheets, which store the questions of for this category.

      descriptionmandatorystring | int

      A short description of the category. This is later shown as the description for the category in moodle and in excel2moodle

      pointsoptionalfloat

      The total points for each question of this category. If omitted, the Fallback points as specified in settings sheet is used.

  3. At least one category sheet, which stores all the questions in for that category.

    • The name of this sheet(s) must be as specified by category sheet

    • The first column of this sheet list all the tags (refer to: Question Tag Definitions) in each of the folowing columns the values for each of the questions are provided.

Settings tag definitions

Below is a list of all supported settings which can be set in the settings sheet. None except categoriesSheet has to be set, if the defaults are to your liking. The settings can either be set project wide in the settings sheet, or you can set them in the categoriesSheet per category, or only for a question, by specifying a setting in addition to the question tags. (see Question Tag Definitions)

The most specific setting definition will always be used. This means when you set the Image Width=500 inside the settings and set Image Width=900 for category05 and set Image Width=600 for question 0501 (inside category05, see Question ID convention) All questions will have a picture with a width of 500px, except those in category will have a width of 900px. But the question 0501 will use the width of 600px.

Note

All tags are not case-sensitive even if they are written here in camelCase. The even may contain spaces and still will be valid.

For example you can write Img Width and it will be recognised as the settings tag imgWidth below.

categoriesSheetmandatorystrdefault=Kategorien

The name of the sheet where the categories are specified. Can not be set category wide

imgFolderstrdefault=imgs

Folder where the images are stored, relative to the folder of the spreadsheet. Inside this Folder for each category a folder needs to be created with the same name as the category sheet. Can not be set category wide

pointsfloatdefault=1.0

The number of points for each correctly answered question.

toleranceintdefault=1

Tolerance for the NF or NFM or CLOZE questions. See also tolerance for details of how this value is treated.

imgWidthintdefault=500

Width of the picture in the questions

answerImgWidthintdefault=120

Width of the pictures set as answers for tho MC questions.

wrongSignCountintdefault=0

The percentage of points that are given for results with the wrong sign. A value of 50 will give \(50\%\) of the total points. Currently only supported for the Cloze question Type

importModulestrdefault=None

A custom python module can be imported to be used in the NFM or CLOZE questions equation. This module must be in the same folder as the spreadsheet. For example, if importModule = my_module (my_module.py) defines a function def myFunc(variableA=1, variableB=1)-> float: You can call this function in the result field of the question via: my_module.myFunc(variableA=a, variableB=b) assuming you are defining the variables a and b in the bulletPoint, the return Value of myFunc() will be used as the result.

Question ID convention

Each Question gets an unique 4 digit identifier consisting of two parts.

  1. part is the two digit category number

  2. part is the two digit question number.

Beispiel: 0613 ist die Frage 13 aus der Kategorie 6

Supported Question Types

MCMultiple Choice

This is the multiple choice question Type. It supports a main text and two list of answers. One with correct and one with incorrect answers. Optionally a picture can be shown below the main text. See Question Tag Definitions for an overview what needs to be specified. Examples for this are given here: Multiple Choice Fragen.

NFNumericEinfach Berechnet

This is a question which expects an numeric answer. It consist of the main text, optionally a bullet Point list, where variables may be presented in a structured way. Optionally a picture can be included. See Question Tag Definitions for an overview what needs to be specified.

NFMNumeric (multiple Variants)Einfach Berechnet (mehrfache Variation):

This is an easy way to serialize numerical Questions based on a set of Variables on which a result is calculated by excel2moodle When imported into moodle it is the same as the NF Question. When creating this question mostly the same rules apply as for the NF type. But instead of specifying the numeric answer, a formula is given, which will evaluated by excel2moodle Inside the formula variables need to be used. For each of these variables another tag is created in the spreadsheet to which a list of numbers need to be passed. Examples for this are given here: Numerische Fragen.

CLOZELückentext

This Question Type lets you ask more than one MC or NFM question fields in one moodle question. For the NFM question, you define the variables as you are used to. But then you can define multiple result:i formulas, where i is the number of the answer part. The corresponding question to result:2 must be asked in the tag part:2 The first Result for this formula has to be defined in firstResult:2 tag.

If you want to ask MC questions in the answer Parts, you define true:2 and false:2 for each of the answer options for the second question part. Examples for this are given here: Example of CLOZE questions.

Question Tag Definitions

When writing questions several important things must be considered, as the program still is quite buggy and likely to crash, when unexpected input is reached.

Important

The following list shows all tags that need or may be declared in the spreadsheet. It has the following structure

tagclassifierQuestionType

Description of what this tags does

The first classifier shows weather this tag is optional or mandatory. Each tag may have slightly different meanings for each question type.

For example: the bulletPoints tag is mandatory for NFM questions, but for NF questions it is optional.

General tags for all Question Types

numbermandatoryALL

The number of the question

typemandatoryALL

Any of NFM, NF, MC, CLOZE Defines the type of the question.

namemandatoryALL

The internal name of the question, not the title that will be seen when the question is viewed

textmandatoryALL

The question text. At least one of these must be filled out. If it occurs more than once, a new paragraph gets added for each.

pictureoptionalALL

If left empty, no picture will be included All pictures have to be in the folder specified in the settings by the imgFolder Tag (see also: Settings tag definitions) If a picture with the same ID as the question should be included, true or yes or the number of the question can be written here. If a picture from another question (in the same category) should be used, write the number of that question: The Width or Height of the question can be set by appending :width:600 to the number.

Examples (for question 2 inside category category06, see also: Question ID convention):

  • true will include the picture imgFolder/category06/0602.jpg.

  • 15:width:650 will include the picture imgFolder/category06/0615.jpg and set its width to 650 px.

  • 1_a:height:90 will include the picture imgFolder/category06/0601_a.jpg and set its height to 90 px.

Specific tags for question Types

truemandatoryMC

One true answer. For each true answer this tag can be specified.

falsemandatoryMC

One false answers. For each wrong answer this tag can be specified.

answerTypemandatoryMC

controls the formatting of the answers either text, formula, unit, or picture

bulletPointoptionalNF

Bullet Points are typeset by excel2moodle using latex. They are intended to list parameters. One bullet point consists of four parts

  1. Name: (Example: length)

  2. Variable Name: (Example l )

  3. =-Sign

  4. Value: (Example 5.4)

  5. Si-Unit: (Example m)

This would be typeset: \(\cdot \mathrm{length} l = 5.4 \mathrm{m}\) A list of bullet points can be created by defining multiple times the tag bulletPoint

bulletPointmandatoryNFM

list of bullet points separated by ;, each bullet Points consist of 4 parts: The only difference to the NF bullet point is the use of variables instead of values.

  1. Name: (Example: length)

  2. Variable Name: (Example l )

  3. =-Sign

  4. Variable: (Example {l}): must be one letter enclosed by curly braces

  5. Si-Unit: (Example m)

A list of bullet points can be created by defining multiple times the tag bulletPoint

resultmandatoryNF

the value of the result. Decimal separator must be a . dot.

resultmandatoryNFM

Python expression to calculate the result. Must follow those rules.

  • Decimal separator must be a . dot.

  • Variables must not be encapsulated by {curly} braces.

  • Example: 3.7*a/(b+4*c)-a**2/4 is a valid representation of \(\frac{3.7}{b+4c}-a^{2/4}\)

  • All asteval built in functions are available to be used.

  • Each variable a, b, c needs to be set as a new tag in the spreadsheet. For each of those variables a list of values needs to be given. Each value needs to be separated by a semicolon Each variable name may use any number of letters and numbers, without spaces or special characters.

Hint

All of the asteval built in functions are available to be used And as the code in result is executed in python, you can build quit complex expressions. You can assign variables and use if-else clauses or the min() and max() functions. As this would be quite tedious to write into the spreadsheet cell, you may even specify your own python module to be imported via importModule in the settings. Refer to importModule for more information on this.

Warning

Do not include code you didn’t verify yourself into the ``result`` field, as this could potentially harm your system. excel2moodle uses the ASTEVAL: Minmal Python AST Interpreter, for parsing the expressions. Broadly speaking the code of result is executed in a more isolated environment to not harm your computer. This makes it harder for malicious code being executed. But still there is a potential security risk when executing external code. Therefore always make sure the result field does not contain code, that could harm your machine.

toleranceoptionalNFNFMCLOZE

Tolerance in which the answer is considered to be true. Fraction 0.01 or percent 1 Both would be interpreted as a relative Tolerance of \(\pm 1\%\)

  • Any value below \(1\) will be interpreted as a fraction. \(0.8\) would give \(\pm 80\%\) tolerance.

  • Any value above \(1\) will be interpreted as percentage: \(1.3\) would give \(\pm 1.3\%\) tolerance.

Note

If no tolerance is specified for the question, the tolerance from the category or the settings sheet is used. The tolerance definition in the question takes precedence over that in the category, which takes precedence over the settings tolerance.

unitoptionalNFNFM

‘not yet implemented’

firstResultoptionalNFM

This value is used to verify that the formula inside result with the first set of variables calculates the correct answer. If the calculated value is different from the value specified here, an warning in logged.

Tags for CLOZE question Type

Note

The Cloze Question creates the option of more than one question parts in one Moodle question. The structure for excel2moodle is defined as follows.

  1. You define the question Number an name an its introducing text paragraphs as usually.

  2. For numeric question parts you define the bulletPoint s

  3. For multiple choice question parts, you define true:i and false:i answers.

  4. Then for each of the question parts, you define the tags below

Each tag gets a Number i which represents the parts number starting from 1 For each part you can specify not only a numeric answer, but also multiple choice answers.

part:istr

The question parts text.

result:istr

The answer formula, same as for NFM question: result

firstResult:ifloat

Refer to firstResult