Generating a transition probability matrix in excel

[abberyfarm]

Hi there,

I have a difficult problem.

Would anybody know if or how excel can be used to generate a transition probability matrix of data. It is to be used in a markov chain analysis later but I just wish to find out if excel can be used for now.

I'm thinking maybe it could be done with a pivot table or perhaps an add-in has been developed to do this already.

I have time, speed and acceleration data for a car in three columns. I'm trying to generate a 2 dimensional transition probability matrix of velocity and acceleration.

The concept is given a particular speed and acceleration I want to be able to look at the matrix and identify the most likely (probable) speed and acceleration that could occur next.

I have attached two pictures which illustrate the concept here. It is a secure file sharing site.

http://www.sendspace.com/file/3h7j49
http://www.sendspace.com/file/44myq4
http://www.sendspace.com/file/fo0fnz

I also attached a sample data file if anyone has an idea and wants to experiment

Appreciate any input

John

 

[abberyfarm]

Hello shg,

Sorry for bothering you again. Would you have any thoughts on how to simulate more steps?

Thank you

John

 

[oalomar]

I can not understand what the calculations for the probabilities are. To have a transition probability matrix, should not each cell value (e.g. number of observed transitions from A-->B) be divided by the total number of transitions (19)?
How is it possible that with one transition from '2' to '0', the probability is 'zero'?

Many thanks

​

 

[shg]

To calculate a transition, you generate a random number [0...1) and do a lookup agains the transition probability table.

 

[abberyfarm]

The frequency table is correct but the transition table is wrong - the rows must sum to one. You need to divided each entry in the frequency table by the row sum.

 

[jenelotte]

what is the use of the random number X2? i'm also doing a markov chain analysis.. and also, why are the sum of the probabilities (in a row) in the transition matrix not equal to one? is the error in the formula for Q:V.. thank you so much..

 

[abberyfarm]

This method is incorrect, suggest you use this markov chain addin for excel, it is very good.

ORMM Excel