Hot questions for Using Neural networks in excel
In using the dataset SP500W90 for Artificial Neural Network in SPSS Modeler, I have a simple stream below.
It generate a result of accuracy 90.9%.
I want to output the predicted values side by side with the existing “closing”, however it doesn’t create an Excel file.
Here’s the setting in the Excel node.
How can I output the prediction in Excel file (side by side with the original “closing”)? Thank you.
The images are not being shown for me. But, in order to export to Excel, you have to use a Type node in front of the Export Excel node. You have to set all type of variables inside the Type Node and then export. And if you want to choose the order of appearence of the variables in the Excel file, use the Field Reorder node before the Export Node.
Check the references from IBM for more details:
Hope to have been helpful!
So I’m trying to program a basic Artificial Neural Network using Excel VBA. I’ve been following one example in particular:
I’ve been basing the code off of the Python example in the article (code example is toward the bottom of the article). I don’t have access to Python unfortunately so I’m trying to do this with VBA. I’ve done my best to convert the code into a useable format for Excel, however there is an issue I'm encountering and I'm not sure how to solve it:
Here is my VBA code:
Sub ANN() Dim X(1010, 1011, 101) As Integer 'Input Dim Y(1, 1, 0) As Double 'output for comparison Dim E(0, 0, 0) As Double 'Error 'Variable Initialization Dim Epoch As Long Dim LearnRate As Double Dim InputLayer_Neurons() As Integer 'Number of Features in data set ReDim InputLayer_Neurons(ArrayLen(X)) Dim HiddenLayer_Neurons As Integer Dim Output_Neurons As Integer Dim hidden_layer_input1 As Variant Dim hidden_layer_input As Variant Dim hiddenlayer_activations As Variant Dim output_layer_input1 As Variant Dim output_layer_input As Variant Dim slope_output_layer As Variant Dim slope_hidden_layer As Variant Dim d_output As Variant Dim Output As Variant Dim Wh As Double 'Weight Hidden Layer Dim Bh As Double 'Bias Hidden Layer Dim Wout As Double 'Weight output Layer Dim Bout As Double 'Bias Ouput layer Dim i As Long Epoch = 5000 'Training Iterations LearnRate = 0.1 'Learning Rate HiddeLayer_Neurons = 3 'Number of Neurons in Hidden Layer Output_Neurons = 1 'Number of Neurons at output layer 'Weight & Bias Initialization Wh = Application.WorksheetFunction.RandBetween(InputLayer_Neurons, HiddenLayer_Neurons) Bh = Application.WorksheetFunction.RandBetween(1, HiddenLayer_Neurons) Wout = Application.WorksheetFunction.RandBetween(HiddenLayer_Neurons, Output_Neurons) Bout = Application.WorksheetFunction.RandBetween(1, Output_Neurons) For i = 0 To Epoch 'Forward Propagation hidden_layer_input1 = WorksheetFunction.MMult(X, Wh) hidden_layer_input = hidden_layer_input1 + Bh hiddenlayer_activations = Sigmoid_Activation(hidden_layer_input) output_layer_input1 = WorksheetFunction.MMult(hiddenlayer_activations, Wout) output_layer_input = output_layer_input1 + Bout Output = Derivatives_Sigmoid(output_layer_input) 'Backpropagation E = Y - Output slope_output_layer = Derivatives_Sigmoid(Output) slope_hidden_layer = Derivatives_Sigmoid(hiddenlayer_activations) d_output = E * slope_output_layer Error_at_hidden_layer = WorksheetFunction.MMult(d_output, Transpose(Wout)) d_hiddenlayer = Error_at_hidden_layer * slope_hidden_layer Wout = Wout + WorksheetFunction.MMult(Transpose(hiddenlayer_activations), d_output) * LearnRate Bout = Bout + WorksheetFunction.Sum(d_ouput) * LearnRate Wh = Wh + WorksheetFunction.MMult(Transpose(X), d_hiddenlayer) * LearnRate Bh = Bh + WorksheetFunction.Sum(d_hiddenlayer) * LearnRate Next Debug.Print Output End Sub Function Sigmoid_Activation(X) As Variant Sigmoid_Activation = 1 / (1 + Application.WorksheetFunction.Power(-X)) End Function Function Derivatives_Sigmoid(X) As Double Derivatives_Sigmoid = X * (1 - X) End Function Public Function ArrayLen(arr As Variant) As Integer ArrayLen = UBound(arr) - LBound(arr) + 1 End Function
my Issue is:
In the section under Backpropagation. I am getting a type mismatch error on the line:
E = Y – Output
I suppose this is because in VBA there is no built in function for subtracting a Double type value (Output) from an array (Y). Even though Output is declared as a variant, I believe the value it will hold will contain a floating point value. I'm not sure if this is the reason for the conflict. It seems you can do this in Python however, which is probably why it is used for scientific calculations.
In any case, what would be the best way to resolve this?
Unfortunately VBA does not support direct operations on arrays. Instead you have to loop :(
If you really want to do it with VBA you could have a look at this CodeReview post.
If you want to do it using Excel, but cannot accpet the limitations of VBA, there are a few Python tools allowing to program Excel using Python. A quick search will get you plenty, including https://www.xlwings.org/ which has been on my list of things to try for much too long.
Neural Net Toolbox Feed Forward Network issuing "Output <variable> Not Assigned" error when spreadsheets are used as Training and Validation data
I am trying to create a feed forward network that can fit a set of financial data. The financial data was supplied to us in the form of Excel spreadsheets. I have created smaller spreadsheets that only contain the necessary data. But when I import the training set and validation sets, then try to train the network, I get the following error when I reach the training function:
Output argument "v" (and maybe others) not assigned during call to "network/subsref".
I have checked my code over and done research, but I am unable to determine the problem. The information I found on the error says that it meant that an output argument did not exist, but I cannot see where.
Relevant code snippets:
training_patterns = xlsread('Training_Set'); validation_patterns = xlsread('Validation_Set'); ndim_inputs=2; %2D patterns--not counting bias nnodes_layer1=5; %try this many interneurons--not including bias nnodes_layer2=1; %single output net = feedforwardnet(5, 'trainlm'); [net, tr] = net.train(net, training_patterns, validation_patterns);
By the way, the matrices that contain the data are of substantial size. The training matrix is 227x8 and the validation matrix is 51x8.
I've never seen the Neural Network Toolbox being used that way. Try using just the
train method rather than just
net = train(net, training_patterns, validation_patterns);