Deploy CNTK model to Excel using C#

In the last blog post, we saw how to save model state (checkpoint) in order to load it and train again. Also we have seen how to save model for the evaluation and testing. In fact we have seen how to prepare the model to be production ready.

Once we finish with the modelling process, we enter in to production phase, to install the model on place where we can use it for solving real world problems. This blog post is going to describe the process how deployed CNTK model can be exported to Excel like as AddIn and be used like ordinary Excel formula.

Preparing and deploying CNTK model

From the previous posts we saw how to train and save the model. This will be our starting point for this post.

Assume we trained and saved the model for evaluation from the previous blog post with file name as “IrisModel.model”. The model calculates Iris flower based on 4 input parameters, as we saw earlier.

  1. The first step is to create  .NET Class Library and install the following Nugget packages
    1. CNTK CPU Only ver. 2.3
    2. Excel Dna Addin
    3. Include saved IrisModel.model file in the project as Content and should be copied in Debug folder of the application.

As we can see, for this export we need Excel Dna Addin, fantastic library for making anything as Excel Addin. It can be install as Nuget package, and more information can be found at

The following picture shows above 3 actions.

Once we prepare everything, we can start with the implementation of the Excel Addin.

  1. Change the Class.cs name into IrisModel.cs, and implement two methods:
    1. public static string IrisEval(object arg) and
    2. private static float EvaluateModel(float[] vector).

The first method is direct Excel function which will be called in the excel, and the second method is similar from the previous blog post for model evaluation. The following code snippet shows the implementation for the methods:

[ExcelFunction(Description = "IrisEval - Prediction for the Iris flower based on 4 input values.")]
public static string IrisEval(object arg)
        //First convert object in to array
        object[,] obj = (object[,])arg;

        //create list to convert values
        List<float> calculatedOutput = new List<float>();
        foreach (var s in obj)
            var ss = float.Parse(s.ToString(), CultureInfo.InvariantCulture);
        if (calculatedOutput.Count != 4)
            throw new Exception("Incorrect number of input variables. It must be 4!");
        return EvaluateModel(calculatedOutput.ToArray());
    catch (Exception ex)
        return ex.Message;

private static string EvaluateModel(float[] vector)
    //load the model from disk
    var ffnn_model = Function.Load(@"IrisModel.model", DeviceDescriptor.CPUDevice);

    //extract features and label from the model
    Variable feature = ffnn_model.Arguments[0];
    Variable label = ffnn_model.Output;

    Value xValues = Value.CreateBatch<float>(new int[] { feature.Shape[0] }, vector, DeviceDescriptor.CPUDevice);
    //Value yValues = - we don't need it, because we are going to calculate it

    //map the variables and values
    var inputDataMap = new Dictionary<Variable, Value>();
    inputDataMap.Add(feature, xValues);
    var outputDataMap = new Dictionary<Variable, Value>();
    outputDataMap.Add(label, null);

    //evaluate the model
    ffnn_model.Evaluate(inputDataMap, outputDataMap, DeviceDescriptor.CPUDevice);
    //extract the result  as one hot vector
    var outputData = outputDataMap[label].GetDenseData<float>(label);
    var actualLabels = outputData.Select(l => l.IndexOf(l.Max())).ToList();
    var flower = actualLabels.FirstOrDefault();
    var strFlower = flower == 0 ? "setosa" : flower == 1 ? "versicolor" : "virginica";
    return strFlower;
}<span style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" data-mce-type="bookmark" class="mce_SELRES_start"></span>

That is all we need for model evaluation in Excel.

Notice that the project must be build with the x64 architecture, and also installed Excel must be in x64 version.  This demo will not work in Excel 32 bits.

Rebuild the project and open Excel file with Iris Data set. You can use the file included in the demo project,  specially prepared for this blog post.

  • Got to Excel – > Options -> Addins,
    • Install the ExportCNTKToExcel-AddIn64-packed addin file.

  • Start typing the Excel  formula :
    • IrisEval(A1:D1) and press Enter. And the magic happen.


Complete source code for this blog post can be found here.



How to save CNTK model to file in C#

Final process of training is the model which should be used in the production as safe, reliable and accurate. Usually model training is frustrating and time consuming process, and it is not like we can see as demo to introduce with the library. Once the model is built with right combination of parameter values and network architecture, the process of modelling turn in to interesting and funny task, since it calculates the values just as we expect.

In most of the time, we have to save the current state of the model, and continue with training due to various reasons:

  • to change the parameters of the learner
  • to switch from one to another machine,
  • or to share the state of the model with your team mate,
  • to switch from CPU to GPU,
  • etc

In all above cases the current state of the model should be saved, and continue the training process from the last stage of the model. Because, the training from the beginning is not a solution, since we already invest time and knowledge to achieve progress of the model building.

The CNTK supports two kind of persisting the model.

  • production/ready or evaluation ready state, and
  • saving the checkpoint of the model for later training.

In the first case, the model is prepare for the evaluation and production but cannot be trained again, because it is freed from all other information but for the evaluation. During the saving process, only one files is generated.

In the second case, beside a model file, another file is generated with the name “modelname.ckp”. The file contains all information needed for the continuation of training.  Once the trainer  checkpoint is persisted we can continue with model training even if we changed the following:

  • the training data set with the same dimensions and data types
  • the parameters of the learner,
  • the learner

What we cannot change in order to continue with training is the the network model. In other words, the model must remain with the same number of layers, input and output dimensions.

Saving, loading and evaluating the model

Once the model is trained it can be persisted as separated file. As separate file, it can be loaded and evaluated with different dataset, but the number of the features and the label must remain the same as in case when was trained. Use this method when you want to share the model with someone else, or when you want to deploy the model in the production.

The model is saved simply by calling the CNTK method  Save:

public void SaveTrainedModel(Function model, string fileName)

The model evaluation requires several steps:

  • load the model from the file,
  • extract the features and label from the model
  • call evaluate method from the model, by passing the batch created from the features, label and the evaluation dataset.

The model is loaded by calling Load method.

public Function LoadTrainedModel(string fileName, DeviceDescriptor device)
   return Function.Load(fileName, device, ModelFormat.CNTKv2);

Once the model is loaded, features and label are extracted from the model on the following way:

//load the mdoel from file
Function model = Function.Load(modelFile, device);
//extract features and label from the model
Variable feature = ffnn_model.Arguments[0];
Variable label = ffnn_model.Output;

The next step is creating the minibatch in order to pass the data to the evaluation.In this case we are going to create only one row for the Iris example of:

//Example: 5.0f, 3.5f, 1.3f, 0.3f, setosa
float[] xVal = new float[4] { 5.0f, 3.5f, 1.3f, 0.3f };
Value xValues = Value.CreateBatch<float>(new int[] {feature.Shape[0] }, xVal, device);
//Value yValues = - we don't need it, because we are going to calculate it

Once we created the variable and values we can map them and pass to the model evaluation, and calculate the result:

//map the variables and values
var inputDataMap = new Dictionary<Variable, Value>();
var outputDataMap = new Dictionary<Variable, Value>();
outputDataMap.Add(label, null);
//evaluate the model
ffnn_model.Evaluate(inputDataMap, outputDataMap, device);
//extract the result  as one hot vector
var outputData = outputDataMap[label].GetDenseData<float>(label);

The evaluation result should be transformed to proper format, and compared with expected result:

//transforms into class value
var actualLabels = outputData.Select(l => l.IndexOf(l.Max())).ToList();
var flower = actualLabels.FirstOrDefault();
var strFlower = flower == 0 ? "setosa" : flower == 1 ? "versicolor" : "versicolor";
Console.WriteLine($"Model Prediction: Input({xVal[0]},{xVal[1]},{xVal[2]},{xVal[3]}), Iris Flower={strFlower}");
Console.WriteLine($"Model Expectation: Input({xVal[0]},{xVal[1]},{xVal[2]},{xVal[3]}), Iris Flower= setosa");

Training previous saved model

Training previously saved model is very simple, since it requires no special coding. Right after the trainer is created with all necessary stuff (network, learning rate, momentum and other),
you just need to call


No additional code should be added.
The above method is called, after you successfully saved the model state by calling


The method is usually called at the end of the training process.
Complete source code from this blog post can be found here.

How to setup learning rate per iteration in CTNK using C#

So far we have seen how to train and validate models in CNTK using C#. Also there many more details which should be revealed in order to better understand the CNTK library. One of the important feature not only in the CNTK but also in every DNN (deep neural networks) is the learning rate.

In ANN the learning rate is the number by which the derivative is multiply before it is subtracted by the weight. If the weight is decreased to much the loss function will be increased and the network will diverge. On the other hand if the weight is decreased to little the loss function will be changed little and the diverge progress will be to slow. So selecting the right value of the parameter is important. During the training process, the learning rate is usually defined as constant value. In CNTK the learning rate is defined as follow:

// set learning rate for the network
var learningRate = new TrainingParameterScheduleDouble(0.2, 1);

From the code above the learning rate is assign to 0.2 value per sample. This means whole training process will be done with the learning rate of 0.2.
The CNTK support dynamic changing of the learning rate.
Assume we want to setup different the learning rates so that from the fist to the 100 iterations the learning rate would be 0.2. From the 100 to 500 iterations we want the learning rate would be 0.1. Moreover, after the 500 iterations are completed and to he end of the iteration process, we want to setup the learning rate to 0.05.

Above said can be expressed:

lr1=0.2 , from 1 to 100 iterations

lr2= 0.1 from 100 to 500 iterations

lr3= 0.05 from 500 to the end of the searching process.

In case we want to setup the learning rate dynamically we need to use the PairSizeTDouble class in order to defined the learning rate. So for the above requirements the flowing code should be implemented:

PairSizeTDouble p1 = new PairSizeTDouble(2, 0.2);
PairSizeTDouble p2 = new PairSizeTDouble(10, 0.1);
PairSizeTDouble p3 = new PairSizeTDouble(1, 0.05);

var vp = new VectorPairSizeTDouble() { p1, p2, p3 };
var learningRatePerSample = new CNTK.TrainingParameterScheduleDouble(vp, 50);

First we need to defined PairSizeTDouble object for every learning rate value, with the integer number which will be multiply.
Once we define the rates, make a array of rate values by creating the VectorPairSizeTDouble object. Then the array is passed as the first argument in the TrainingParameterScheduleDouble method. The second argument of the method is multiplication number. So in the first rate value, the 2 is multiple with 50 which is 100, and denotes the iteration number. Similar multiplication are done in the other rate values.

Testing and Validation CNTK models using C#

…continue from the previous post.
Once the model is build and Loss and Validation functions are satisfied our expectation, we need to validate and test the model using the data which was not part of the training data set (unseen data). The model validation is very important because we want to see if our model is trained well,so that can evaluates unseen data approximately same as the training data. Otherwise the model which cannot predict the output is called overfitted model. Overfitting can happen when the model was trained long enough that shows very high performance for the training data set, but for the testing data evaluate bad results.
We will continue with the implementation from the prevision two posts, and implement model validation. After the model is trained, the model and the trainer are passed to the Evaluation method. The evaluation method loads the testing data and calculated the output using passed model. Then it compares calculated (predicted) values with the output from the testing data set and calculated the accuracy. The following source code shows the evaluation implementation.

private static void EvaluateIrisModel(Function ffnn_model, Trainer trainer, DeviceDescriptor device)
    var dataFolder = "Data";//files must be on the same folder as program
    var trainPath = Path.Combine(dataFolder, "testIris_cntk.txt");
    var featureStreamName = "features";
    var labelsStreamName = "label";

    //extract features and label from the model
    var feature = ffnn_model.Arguments[0];
    var label = ffnn_model.Output;

    //stream configuration to distinct features and labels in the file
    var streamConfig = new StreamConfiguration[]
            new StreamConfiguration(featureStreamName, feature.Shape[0]),
            new StreamConfiguration(labelsStreamName, label.Shape[0])

    // prepare testing data
    var testMinibatchSource = MinibatchSource.TextFormatMinibatchSource(
        trainPath, streamConfig, MinibatchSource.InfinitelyRepeat, true);
    var featureStreamInfo = testMinibatchSource.StreamInfo(featureStreamName);
    var labelStreamInfo = testMinibatchSource.StreamInfo(labelsStreamName);

    int batchSize = 20;
    int miscountTotal = 0, totalCount = 20;
    while (true)
        var minibatchData = testMinibatchSource.GetNextMinibatch((uint)batchSize, device);
        if (minibatchData == null || minibatchData.Count == 0)
        totalCount += (int)minibatchData[featureStreamInfo].numberOfSamples;

        // expected labels are in the mini batch data.
        var labelData = minibatchData[labelStreamInfo].data.GetDenseData<float>(label);
        var expectedLabels = labelData.Select(l => l.IndexOf(l.Max())).ToList();

        var inputDataMap = new Dictionary<Variable, Value>() {
            { feature, minibatchData[featureStreamInfo].data }

        var outputDataMap = new Dictionary<Variable, Value>() {
            { label, null }

        ffnn_model.Evaluate(inputDataMap, outputDataMap, device);
        var outputData = outputDataMap[label].GetDenseData<float>(label);
        var actualLabels = outputData.Select(l => l.IndexOf(l.Max())).ToList();

        int misMatches = actualLabels.Zip(expectedLabels, (a, b) => a.Equals(b) ? 0 : 1).Sum();

        miscountTotal += misMatches;
        Console.WriteLine($"Validating Model: Total Samples = {totalCount}, Mis-classify Count = {miscountTotal}");

        if (totalCount >= 20)
    Console.WriteLine($"------TESTING SUMMARY--------");
    float accuracy = (1.0F - miscountTotal / totalCount);
    Console.WriteLine($"Model Accuracy = {accuracy}");


The implemented method is called in the previous Training method.

 EvaluateIrisModel(ffnn_model, trainer, device);

As can be seen the model validation has shown that the model predicts the data with high accuracy, which is shown on the following picture.

This was the latest post in series of blog posts about using Feed forward neural networks to train the Iris data using CNTK and C#.

The full source code for all three samples can be found here.