Extending the functionality

Quantitative Commons comes with a lot of useful tools. For many use cases however, there is need for advanced custom functionality that is not offered by this or any other software. A key feature of Quantitative Commons is the simplicity with which custom tools can be added and shared.

There are two methods to add tools: updating and replacing the QCTools library, which is where the standard tools live, or scripting new tools in either C# or Python.

Updating the QCTools library

You may acquire a copy of the QCTools library and edit it in a code editor of your choice, like Visual Studio or VS Code. A new tool is created by adding to the library a new C# method with the following structure:

private void MyCustomTool()

        {   

            if(gui)

            {

            }

            else

            {

            }

        }

The top part of the method, run if the Boolean variable gui is true, handles the user controls that should be presented to a user who selects the tool. The bottom part of the method handles the actual transformation logic of the method, and is run when a user clicks Execute operation.

The gui part

In the gui part, you will add user controls by calling the CreateControlInfoAndAddToList method. The method takes five arguments:

  • The type of user control. Select a textbox (InputTextBox1), a combobox (InputComboBox1), a DateTimePicker (InputDateTimePicker1), a listbox with checkboxes (InputListBox1), a datagrid (InputDataGrid1), a label (InputTextBlock1) or a button (InputButton1). The button can only be used to browse for files.

  • The watermark text that should be displayed by the control

  • A Boolean value (true or false) controlling whether the control should be populated with the column headers of loaded data in the data pane.

  • A Boolean value (true or false) controlling whether the control should be populated with the row values of the currently selected column in the data pane.

  • A list<string> used to populate the control. Set to null if not applicable.

The creation of input controls should be preceded by a line of code emptying the current list of user controls, and succeeded by a line of code providing a description for the tool. Setting the input controls for a tool that prints a user defined value in the currently selected column in either capitals or lower-case letters could look like:

if(gui)

      {

         List<string> stringInput = new List<string>();

         stringInput.Add("Capitals");

         stringInput.Add("Lower case");

         interf.ControlInfoList.Clear();

         interf.CreateControlInfoAndAddToList("InputTextBox1", "Word to print", false, false, null);

         interf.CreateControlInfoAndAddToList("InputComboBox1", "Letter case", false, false, stringInput);

         interf.DescriptionText = "Writes the 'Word to print' to selected column";

      }

The logic part

When creating the business logic of your new tool, you are quite free to write it anyway you want. The object of your code is to manipulate an in-memory datatable referenced by interf.mainTable. Any user input entered in the application GUI is available in a list<object> named input. You may reference input by its index (determined by the order of the user input controls) and cast it to an appropriate type. The index of the currently selected column in the data pane can be referenced by the int variable selColumn.

When iterating the rows of interf.mainTable, you may add a small snippet of code that makes execution of your logic dependent on any condition set using tools in the Conditions category. Logic for the tool that prints a user defined value to the selected column in either capitals or lower case letters, using the conditionality snippet, could be written as:

else

{

     string wordToprint = input[0].ToString();

     string letterCase = input[1].ToString();

     int rowIndex = -1;

     foreach (DataRow row in interf.mainTable.Rows)

     {

     //condition snippet

          if (interf.ConditionSet)

          {

               rowIndex++;

               if (interf.ConditionTrue[rowIndex] == false)

               continue;

          }

     //end of condition snippet

          if (letterCase == "Capitals")

               row[selColumn] = wordToprint.ToUpper();

          else

               row[selColumn] = wordToprint.ToLower();

     }

}

Do note that iterating the datatable may not be the fastest way of accomplishing this task.

The example tool is of course a very simple one, it serves only to demonstrate the principles of adding tools. You may write as complex tools as necessary, create helper methods and reference external libraries as you see fit. There are also many particular aspects about how tools interact with the Quantitative Commons user interface. You may, for example, prompt a tool to visualize its results on a map or in a graph. These aspects are best learnt by studying existing tools in the library.

Updating Quantitative Commons with new tools

To add your custom tools to Quantitative Commons, you must do two things: replace the QCTools library with your amended version, and add the tool to the appropriate menu.

To replace the QCTools library, select the Add tools-section of the Settings menu. Select Update toolset from file and click Replace QCTools-version to browse for your compiled and saved version of the library. You must restart Quantitative Commons with administrator privileges to execute the swap.

To add new tools to menus, use an SQLite management tool of you choice to view the Quantitative Commons internal database located at C:\Users\Public\QCData. Add your tools to the Tools-table. The ToolId-column should contain the name of your tool (e.g. MyCustomTool), the ToolName-column should contain the name shown to the user (e.g. My custom tool). The Category-column controls which menu the tool should belong to, and the DisplayOrder-column controls the index of your tool in the list of tools belonging to this category. The Conditional-column controls whether your tool is subject to conditions and the Active-column controls whether your tool should be visible in menus. The ToolType should be Native, when belonging to the QCTools library.

Scripting new tools in C# or Python

Scripting tools in Quantitative Commons is quite similar to writing tools directly in the QCTools library, but you do not have to compile anything separately, and there is a user interface to create all but the business logic of your new tools. Do note however, that the scripting interface writes to C:\Users\Public\QCData\PythonScripts (for Python) and C:\Users\Public\QCData\CustomTools.cs (for C#) and scripted tools may easily be further developed (or developed from scratch) in a Python or C# code editor of your choice.

Creating a new script

To start building a new tool, select Add tools from the Settings menu, and expand the Script a new tool-section. Choose to write your tool in C#, Python using IronPython or Python using an installed Python interpreter. IronPython is an implementation of the Python language targeting .NET, it is the faster option for Python scripts and it gives you access to .NET-classes (like the datatable). Also it does not require Python to be installed. The downside is that you can only write native Python code, external Python libraries (including Pandas) will not be available. If you choose to use an installed Python interpreter you can use any external Python library (and pandas must be installed). The downside to this option, aside from needing Python to be installed, is that Quantitative Commons and Python will communicate via a file existing very briefly in the background. This may somewhat affect performance.

When you have selected a language option, you may enter tool- and display names, select a category and give your tool a description. Then click Generate scaffold code to create some code to start from.

Design user input controls

Next, you can design user input controls for your new tool. Expand the Design user input controls-section and select a tool type (Textbox, Combobox, DateTimePicker, Textblock, Listbox, Datagrid or Browse button). Enter watermark text for your control, and check relevant checkboxes depending on if and how you want to populate your control with initial values. Checking the first checkbox populates the control with the column headers of loaded data. Checking the second checkbox populates the control with the row values of the currently selected column. Checking the third checkbox populates the control with a list of static values (when checked controls to add static values are visible). Finally, click Add user control and repeat until you have the controls that you need. Each added control will be reflected in the scaffold code in the code editor.

Adding code snippets

To help you get started, you may expand the Add code snippets-section to insert some pre-written logic into your script. Some snippets are only available for the C# option. Create variables from user input will automatically create variables representing input from your user input controls. Note that they will be strings by default, explicitly cast them to other datatypes if necessary. Iterate rows of loaded data will create an iteration over the rows of the in-memory table holding the data that the tool should transform or create calculations from. This option may also be subject to conditionality (see Updating the QCTools library above). Prepare SQL to datatable will set up a query against the in-memory datatable and Create database connection will prepare a connection to a database. Create visualization will add code to have your tool display its result in a visualization, like a map, a link chart, an XY chart etc.

Writing the business logic and saving the tool

After designing user input and adding relevant code snippets, you can then finalize your script. A script created using C# will be practically identical to the one described in the Updating the QCTools library-section above. The corresponding IronPython script (that directly uses a .NET datatable) would be:

# coding=utf-8
import clr
clr.AddReference('System.Data')
from System import Data
from System.Data import DataTable

#Control:(Textbox), Word to print, false, false, null);
#Control:(Combobox), Letter case, false, false, ControlInput2);
#ControlInput2(Capitals,Lower case)
#ToolDescription: Writes the 'Word to print' to selected column

Wordtoprint = str(input[0])
Lettercase = str(input[1])

for row in dt.Rows:
     if Lettercase == 'Capitals':
          row[selColumn] = Wordtoprint.upper()
     else:
          row[selColumn] = Wordtoprint.lower()

Do please notice that automatic dependence on conditionality tools is not available for Python scripts.

Using an installed python interpreter, the script code would look like:

# coding=utf-8
import pandas as pd
import sys
#Control:(Textbox), Word to print, false, false, null);
#Control:(Combobox), Letter case, false, false, ControlInput3);
#ControlInput3(Capitals,Lower case)
#ToolDescription: Writes the 'Word to print' to selected column

selCol = int(sys.argv[1])
if len(sys.argv)>2:
     input = sys.argv[2]
     input2 = input.replace("$_$", " ")
     inputList = input2.split("#ยค#")
     for i, s in enumerate(inputList):
          if('#%#' in inputList[i]):
               inputList[i] = s.split("#%#")
dt = pd.read_csv('C:\\Users\\Public\\QCData\\tempcsv.csv', sep=';', encoding='latin_1')

#Tool code starts here
Wordtoprint = str(inputList[0])
Lettercase = str(inputList[1])

try:
     for i, row in dt.iterrows():
          if Lettercase == 'Capitals':
               dt.iat[i, selCol] = Wordtoprint.upper()
          else:
               dt.iat[i, selCol] = Wordtoprint.lower()

except Exception as e:
     print(e)
#Tool code ends here

dt.to_csv('C:\\Users\\Public\\QCData\\tempcsv.csv', sep=';', encoding='utf-8', index=False)

As you can see, most of the code above is automatically created boilerplate code used for communication with Quantitative Commons. Only the code between the #Tool code-comment lines needs to be manually written.

When you are satisfied with your tool, click Write tool to file to save it. It will automatically be added to the relevant menu of Quantitative Commons. Any Python script created this way can be found at C:\Users\Public\QCData\PythonScripts. These files can be further developed in any script editor, or shared with other users who can simple copy them to the same location (see above on how to manually add tools to menus, replacing 'Native' with 'Script').

To write C# script code to file, you must use Quantitative Commons with administrator privileges. If you want to develop your C# script outside of Quantitative Commons, you can make changes directly to the .cs-file found at C:\Users\Public\QCData, and compile it using the Write tool to file-button (after selecting the C#-language).

Quantitative Commons

hello@quantitativecommons.com