Edit 02/05/2016: update on a method to avoid using a third party add-in to process the encryption – Send Status Update (Tweets) to Twitter using Excel and VBA, XML requesting the Twitter API with HMAC-SHA1 encryption

I created a Twitter Bot (bot being a shortcut for robot) to send to Twitter periodic status updates (ie Tweets) with Commodities Futures prices like the CBOT(Chicago Board of Trade, actually CME, Chicago Mercantile Exchange) Wheat, Corn, Soybeans, MGE (Minneapolis Grain Exchange) Wheat, Kansas City Board of Trade (KCBT, now part of the CME), the still called Paris MATIF Wheat (NYSE Euronext) and London Feed Wheat (LIFFE, now ICE),… Also I send some FX quotes but the possibilities are virtually infinites! Have a look, it’s @VinzEnergyBot (https://twitter.com/VinzEnergyBot)

The process is simple on the paper, a macro on an Reuters Excel spreadsheet is sending the tweet, and the macro is lauched by a VBS Script triggered by the Windows Task Scedduller. See my previous post for the process.

I used to use a spreadsheet available for free… But since Twitter user @Mr_ExcelTweets (https://twitter.com/Mr_ExcelTweets) is charging a fee to use the service (which works really well that being said, have a look here: http://exceltweets.com/) I had as a challenge to mange to reproduce the principle by myself for free. Just for the sake of intellectual challenge, Adrianus of ExcelTweets is charging 100 euros and it really worth it to my mind. Feel free to contact him (this is an altruistic recommendation): support@excelteets.com

I got a solution. But to cut it short, it is free indeed… For 2 months! (Trial Version) Unless you’re really good at programming cryptographic keys (we’ll talk about it later), it requires downloading an add-in, CryptoSys API (http://www.cryptosys.net/) which works well and is charged $99.

And funnily enough I used as a base this blog post, posted by Adrianus, so I guess this is the same as Excel Tweets!

Using Excel as Twitter client with OAuth
http://www.twopblog.com/2010/09/using-excel-as-twitter-client-with.html

It’s a very old blog post (5 years ago, I wasn’t even on Twitter!), based on the previous API of Twitter. So it needs to be updated (there was a tentative of updating but I struggled to find my way and I’m not a beginner by any means, but it took me 24 hours to get it work) and to be honest, the step by step process is still quite complicated. So let’s update it and maybe trying to make it more straightforward and explanatory for users not familiar with matters and with a bit of documentation for those who want to go more in depth. But 100% credits to this blog post and Adrianus.

Here is the spreadsheet to download: ExcelVBATweet.xlsm

Step 1: Create application with Twitter

On your Twitter account, you need to create an Application, this will enable a third party program to access the API and do operations on behalf of your Twitter account. This will generate a set of different keys in order to identify you and enable the access.

https://apps.twitter.com/app/new

CreateAnApp

Created

Once the App is created, click on ‘Keys and Access Tokens’ tab. You will find the first two keys (I shortcut them for obvious privacy reasons, but sure you get the picture). The keys will identify you. They are secret, don’t publish them or giving them away. But that’s a layer of security, let’s say someone is stealing them, they would take control of this App but not of your Twitter account and you can delete the App. But avoid it. The ‘Access Level’ should be by default ‘Read and Write’, if not tab ‘Permissions’ is pretty straightforward.

Consumer

Then you need, at the bottom of the page to click on ‘Create my access token’, this will create code to communicate with your App.

Token

And now we have the four important keys, we will need them later:

*** Consumer Key (API Key)
*** Consumer Secret (API Secret)
*** Access Token
*** Access Token Secret

Step 2: Gather the data to connect to the API

Excel

On the blog post mentioned, I could not make it work and had to try a lot of different configurations. The API Method is basically what you want to do, here, posting a Tweet, the method is called quite originally POST. The program is going to build a XML request and need to be sent on the API server, the correct address to use is the following: https://api.twitter.com/1.1/statuses/update.json

The signature method is HMAC-SHA1… Yep… It means Hash Message Authentication Code and Secure Hash Algorithm. It’s basically the method that is going to be used to encrypt and protect the data when the XML request is sent. Wikipedia is a very food start to get more educated about it. We’ll try to go more in depth later.

https://en.wikipedia.org/wiki/Hash-based_message_authentication_code

https://en.wikipedia.org/wiki/SHA-1

The Oath Version, despite the fact it seems to be API Version 1.1, need to be 1.0. And be careful, it needs to be text, else the code will only take ‘1’. To avoid any problem, “1.0” can be directly put in the VBA code.

Step 3: Calculate the timestamp

When you send the XML Request, there’s a timestamp in it. It allows Twitter to filter for bug or old requests. It is basically the number of seconds elapsed since the so called UNIX Epoch, basically the 1st of January 1970. But be careful, this has to be compared he the present time in UTC! I am personally 4 hours in front of UTC, so my Excel formula is the following:

=ROUND((TODAY()-DATE(1970,1,1))*24*60*60+(HOUR(NOW())-4)*60*60+MINUTE(NOW())*60+SECOND(NOW()),0)

Update the bolded ‘4’ according to your time zone. I use a ROUND formula just for bug preventing, better safe than sorry! Our friend created a VBA function,, it is not updated with UTC (or maybe he lives on a time zone that matches the UTC) and I personally prefers having a such formula in Excel, in order to see it moving. It is consuming of course much more resource.

Step 4: Calculate the nonce

A nonce is an almost acronym meaning Number Used Once, once again, Wikipedia is great help:

https://en.wikipedia.org/wiki/Cryptographic_nonce

Every XML request sent, will not only have a timestamp, but also a unique ID. Basically, if your program is jamming and try to send 100 times the same request, because the request has a unique ID, the nonce, it will reject the other requests.

One of the easiest way for this is to create a string variable with all the acceptable characters, here, small letters, capitals letters and digits (62 different possibilities) and to randomly pick one character in it to build a random variable. 32 characters are said to be OK. When you think about it, it’s 62 raised to the power 32 which is more than 2 followed by 57 zeros, that’s a lot of possibilities and the uniqueness can be assumed. However, some say that considering the scale of Twitter, nonce aren’t necessarily actual nonce. With 340,000,000 tweets per day in 2012, at some point a nonce will be used twice, but also, It would require twitter to store all the nonces! So a reset after a certain amount of time is pretty likely.

Anyway. Our friend in his blog post created a 41 long characters variable, by picking randomly in a 63 long variable. 26+26+10=62 only, I don’t know if he did a mistyping or what but there’s a ‘I’ indeed between the ‘U’ and ‘V’.

cString = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUIVWXYZ"

As well, he is picking the characters with its position using the following formula:

nRAND = Int((41 * Rnd) + 1)

Great methodology, however, the maximum of the variable nRand will be 42. In Other words, he’s never going to pick after the capital ‘F’. Why the ‘+1’? In the event of int((41 * Rnd)) being 0, he’d have a bug indeed without the +1. The only reason I can think of is a mixup of the length of the output variable and the length of the variable he’s picking it up.

I have therefore modified the code. It doesn’t work better by any means but I find it prettier and I do not like redundancy or uselessness: I want the capital ‘Z’ to be able to be picked! Poor thing! And I prefer checking if it’s 0 before adding 1 but it doesn’t really change anything, just different coding habits I guess.

Function get_oauth_nonce()

cString = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
nLen = Len(cString)
cOauth_nonce = ""

For nCount = 1 To 41
    nRand = Int((62 * Rnd))
    If nRand = 0 Then
        nRand = 1
    End If
    cOauth_nonce = cOauth_nonce + Mid(cString, nRand, 1)
Next

get_oauth_nonce = cOauth_nonce

End Function

Step 5: Get together the base string and the composite signing key

There’s a first problem here. When sending text in a request, only small letters, capital letters, digits, ‘-‘ (minus), ‘.’ (dot), ‘_’ (underscore) and ‘~’ (tilde) can be sent as they are. The other need to be sent in their hexadecimal code, for example %2B will be ‘+’ (plus) or %26 will be ‘&’ (ampersand). Equivalence table can be found there: http://www.asciitable.com/

The function URLEncode to filter and replace characters by their code when it’s needed, I would not have written it better.

Back to the base string, it is basically most of our data joint and liked to each other. It’s creating a quite long string so it can be messy. Although he coded it in 9 lines in could have be done in 1 very long line but for clarity sake, he probably has done the good choice.

As far as the so called composite signing key is concerned, it is just the Consumer Secret (API Secret) linked with an ampersand (‘&’) to the Access Token Secret.

Step 6: The encryption

This step is the reason why it’s totally acceptable to pay a few bucks to subsidize the work if you are not a programmer! The couple base string is encrypted and the key is the composite signing key. When sent online, if someone catches the encrypted base string, it will be a tough job to hack it because only Twitter knows the composite signing key. When the encrypted base string reaches the Twitter server, it’s decrypted with the key. And there, we’re back to the HMAC-SHA2. We’ll shortcut this for now then.

And this is where the CryptoSys API (http://www.cryptosys.net/) is needed. Trial version can be downloaded there: http://www.cryptosys.net/api.html. Once installed, the module basCryptoSys needs to be added in the project in the Visual Basic Editor (it’s located in the subfolder VB6 of the path you installed CryptoSys). It’s already there in the workbook to download.

It’s what is good with Internet and programming. When you want to do some stuff, there was someone who had the idea first and was keen enough, altruistic enough, to share it. That’s when my trial is over, I’d rather paying 100 euros to http://exceltweets.com/ rather than paying $99 for the full CryptoSys, I probably would not use it for anything else, so for the same price (ish), I’d rather give money to Adrianus.

I had an error by the way. In Visual Basic Editor, menu Tool, References, I had a missing component or whatever, just after I installed CryptoSys. I just unticked the missing component and here is what is ticked.

Components

The authorization signature is as well in the hexadecimal structure but 64 bits encoded, this is what its module 3 is doing, basically the table of encodoing as it’s slightly more complicated than the hexadecimal code. I renamed it the module for clarity sake to Encode, but once again, all the credits to Adrianus on the following blog post (reminder of the address): http://www.twopblog.com/2010/09/using-excel-as-twitter-client-with.html

Step 7: Send the XML request

If everything is done properly, the XML request should be received positively and the Tweet published.

A few step further?

The main macro, to send messages, I put a loop on it, in order to send multiple tweets in one macro.  From Line 33, of the column C, as long as each line is not empty it will send a tweet. This is pretty easy coding.

Line = 1

Do While Sheets("PARAM").Cells(32 + Line, 3) <> ""




Line = Line + 1
Loop

End Sub

The real step further would be to be able to code the encryption, a few more reading to start and maybe it will be the occasion of another post! If Mr_ExcelTweets is not putting its price down (come on pal, you’re doing some economy of scales!), it might worth spending a bit of time on it. Appart from the Wikipedia links, there’s a bit of clues there:

http://vb.wikia.com/wiki/SHA-1.bas

http://stackoverflow.com/questions/10068548/base64-hmac-sha1-string-in-vba

https://msdn.microsoft.com/en-us/library/system.security.cryptography.hmacsha1(v=vs.110).aspx

One thought on “Send Status Update (Tweets) to Twitter using Excel and VBA, XML requesting the Twitter API

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.