Creating simple models of causal network models using NodeXL

Aug 11, 2010 at 1:20 PM

I need to create simple models of causal network using NodeXL, where the nodes = events, and links = causal connections between them.

Mximum size probably no bigger than 20 nodes, 30 links

The link weight = the proposed strength of a casual connection between two events

A node attribute will be a value x describing the relative scale/strength/frequency of that event ( a fuzzy notion, I agree)

This value will initially be 1 for all nodes, but then in the next iteration it would be adjusted to be the sum of the weight of each incoming link * the value of the other node that link connects to.

I think that in Excel you can set up these iterated processes. But I dont know how to do so

What I would like is advice on how to set up such a process in NodeXL worksheets, so that the model could be run for any number of iterations

For example, using  the following simple example network

From To Link strength

A B  0.1

B C  0.7

C D 0.9

D A 0.5

C A 0.2




Aug 12, 2010 at 5:01 PM
Edited Aug 12, 2010 at 5:06 PM


Given the iteration requirements, I don't know how to do this using Excel formulas only. However, it could be easily done in VBA, the programming language built into Excel. Have you used VBA, or do you know someone who can do this for you? The algorithm for a VBA macro would look something like this, in pseudocode:

for each iteration
    for each vertex V in the vertex worksheet
        NewFrequency = 0

        for each edge E in the edge worksheet
            if V is one of E's vertices
                NewFrequency = NewFrequency + E.LinkStrength * OtherVertex.Frequency

        V.Frequency = NewFrequency

The number of iterations could be read from some worksheet cell, or your macro could perform just one iteration. In the second case, you could add your macro to Excel's Quick Access toolbar, then click the toolbar repeatedly to see the results of each iteration

-- Tony

Sep 2, 2010 at 2:38 PM

Thanks Tony. I may seek some help with the approach you outlined above

Since making my post above I came up with a simpler solution that will be easier for my clients and colleagues to understand

This involves setting up a series of fields in the Vertice sheet called Time1, Time2, Time3, etc, plus a single field in the Edges worksheet giving weights to each edge.

Then for a given vertice placing a formula in the T2 column that = value of T1 for the same vertice + (T1 for the linked vertice * the weight for that edge) + (..same for any other linked vertices)

This formula can then be copied over to the adjacent Time3,  etc, columns, with the references moving to the values in T2, etc, so long as the reference to the weighting of the edge remains fixed

The advantage of this crude approach is that I can produce a graph showing how the values for each vertice change over time (some staying same, some increasing, others decreasing (I used positive and negatively value weights)

(It might even be able to take the same approach with the edge, allowing them to change over Time1, Time2, Time3, as a function of some preceding network attribute)

What I like about this sort of thing is the ability to make simple dynamic models within a given network structure. Useful in my line of work, where I need to be able to capture people's relatively simple conceptions of how things work, in network terms, then help them identify what the consequences might be