RabbitMQ and Excel

RabbitMQ is one of the most popular messaging queue systems out there. If you’re a programmer there are client packages available for almost of the programming languages. However if you’re not a programmer your choices are limited in trying to interact with RabbitMQ.

In this blog I wanted to document some of my work in using Excel to read and write messages from RabbitMQ.

Getting Started

There are some great RabbitMQ guides. To get Excel talking to RabbitMQ I used the REST API that will be need to be installed.

For my Excel work I used Visual Basic for Applications (VBA) that is launched by Alt-F11. Next I created a Module (this is a container for VBA code), and I created two functions:

  • RMQ_getqueue – to get the first value in a queue
  • RMQ_exchangeWrite – to write a value to an exchange  (note: if an exchange has a binding to a queue. Writing to an exchange will be also be writing to a queue).

VBA_insert_module

Read Message Queues

For my example I focused on getting the first item in a queue. Message in a queue can be cleared as they are read if the option requeue = False.

 Function RMQ_getqueue(RabbitMQ_Server As String, QueueName As String, Optional rmq_user As String, Optional rmq_pswd As String, Optional requeue As Boolean)  
 ' Create a REST API get request to a Rabbitmq server and get the first item in a queue  
   Dim theJSON As Object, http As Object, scriptControl As Object  
   Dim theurl As String, theparams As String, rqmsg As String  
   Set http = CreateObject("MSXML2.XMLHTTP")  
 ' Use a Jscript eval command to evaluate the JSON, (so you don't have to add a JSON component  
   Set scriptControl = CreateObject("MSScriptControl.ScriptControl")  
   scriptControl.Language = "JScript"  
     
   theurl = "http://" & RabbitMQ_Server & "/api/queues/%2f/" & QueueName + "/get"  
    
 ' define a parameter list this must be JSON and the synax is pickie  
   If IsMissing(requeue) Then requeue = True  
   theparams = "{""count"":1,""requeue"":""" & LCase(requeue) & """,""encoding"":""auto"",""truncate"":50000}"  
   'Debug.Print theparams  
     
   http.Open "POST", theurl, False, rmq_user, rmq_pswd  
   http.setRequestHeader "Content-type", "application/xml"  
   http.send (theparams)  
 ' create a simple JSON string  
   thestr = Mid(http.responseText, 2, Len(http.responseText) - 2)  
   Set theJSON = scriptControl.Eval("(" & thestr & ")")  
   'Debug.Print http.responseText  
   
   RMQ_getqueue = theJSON.payload  
 End Function  

The RMQ_getqueue function creates a URL string that is passed to the http request object. The RabbitMQ response text is JSON formatted. I didn’t want to load external JSON addins so I used the ScriptControl with a Javascript eval function to parse the JSON. The payload item of the response is the first message in the queue.

To use this new function in Excel select the category “User Defined”.

Excel_insert_function

Then fill in the parameters as per your requirement.

Excel_rmq_get

Write a Message to an Exchange

As mentioned earlier you can’t write directly to a queue with the REST API instead you write to an exchange. The exchange has a routing key that “binds” a messages to a queue.

Like the get queue function the RMQ_exchangeWrite function formats a URL with all the required parameters.

 Function RMQ_exchangeWrite(RabbitMQ_Server As String, ExchangeName As String, RouteKey As String, theMessage As String, Optional rmq_user As String, Optional rmq_pswd As String)  
 ' Create a REST API post a new message to a Rabbitmq Exchange with a Routing Key  
   Dim http As Object  
   Dim theurl As String, theparams As String  
   Set http = CreateObject("MSXML2.XMLHTTP")  
     
   theurl = "http://" & RabbitMQ_Server & "/api/exchanges/%2f/" & ExchangeName + "/publish"  
    
 ' define a parameter list this must be JSON and the synax is pickie  
   theparams = "{""properties"":{},""routing_key"":""" & RouteKey & """,""payload"":""" & theMessage & """,""payload_encoding"":""string""}"  
     
   Debug.Print theparams  
     
   http.Open "POST", theurl, False, rmq_user, rmq_pswd  
   http.setRequestHeader "Content-type", "application/xml"  
   http.send (theparams)  
   
   'Debug.Print http.responseText  
   
   RMQ_exchangeWrite = http.statusText & " " & http.responseText  
 End Function  

Like the earlier function, fill in all the parameter as per your system.

Excel_rmq_write

Final Comments

This was an introductory project, and there are still a lot of features and error checking that should be included.

If I had more time I would put some dialogs in to dynamically show queues, exchanges etc.

Curve Fits for Kids

I was helping my daughters with some school projects in graphing, and I thought that it would be good  to document and share our discussion. This blog will look at solving the equations for plots using Desmos, Google Docs and Excel.

Desmos

Desmos (https://www.desmos.com/calculator) is great online free graphing package that is used in many school systems. Desmos has a lot of options to simulate, tweek and view data.

To add a table of data, use the “+” button and select table.

demos1

Once the data table has been entered an f(x) expression can be linked to the data. In the example below a linear expression was used. It’s important to note, two things:

    • The subscript must match. So if the table uses: y2  x2 then the formula needs to use the same.
    • A ~ (tilda) is used instead of an = (equals) sign.

 

demos2

If the formula matches up with the table a plot will be drawn and the slope, intercept and r-squared (goodnes of fit) values will be shown.

We found that for simple first order (straight line curves) Desmos worked really well, however for polynomials and other more advanced equations it was a little awkward.

Google Docs

Our school board uses Google Classroom which is an online suite of tools that enables kids to do Microsoft Excel, Word and Powerpoint in a free web based environment.

The Sheets options (https://docs.google.com/spreadsheets) of Google Docs works very much like Excel. To do some charting, enter a table of data and use the “chart” button to present the desired plot.

In the “Customize” options of the chart, under Series, a trend line can be added with an equation and a R-squared (fitness of curve) text.

googledoc1

It is also possible to use the SLOPE, INTERCEPT and RSQ formulas show the results in a cell.

The regression type supports functions such as log, exponential, and polynomial. For the example below we got the equation for a 2nd order polynomial.

googledoc2

Note: Sheets can import and export to and from Excel.

Excel

Different versions of Excel (and LibreOffice) will have a slightly different way to get the stats on a curve. For our work we used Excel from Office 2016.

In the Chart Elements, under Trend Line, the more Options items will offer features that are almost identical to Google Sheets.

excel1

A type of trend line can be selected and the solved equation and R-squared value can be shown on the plot.

excel2

Advanced equations like higher order polynomials can also be used.

excel3

Excel (and LibreOffice) support function calls for : SLOPE, INTERCEPT and RSQ.

 

Summary

Desmos is an excellent package for kids to play with and understand the different plot types, but it probably isn’t the best tool for doing curve fits of test data for projects.

Excel and Google Sheets are excellent for most charting and plot statistical projects.