This is coming after the following blog entry: “Send Status Update (Tweets) to Twitter using Excel and VBA, XML requesting the Twitter API”. I promised to come back with an update to avoid using a third party add-in on the encryption stage. It took a bit of time but here we are!

Indeed, when my trial of CryptoSys expired, I decided, not only because I’m a cheapskate but also for the intellectual challenge, to look for a free alternative. In other words, manage to find a way to create a signature with the HMAC-SHA1 (Hash Message Authentication Code and Secure Hash Algorithm) method just with Excel and Visual Basic (VBA).

The objective is still to make working my Twitter bot (@VinzEnergyBot) to send MATIF, CBOT, MGEX, KCBOT, ICE and FX quotes on Twitter automatically, without a third party add-in. There’s only one constraint now: having the accurate Excel file opened and schedule a task in the Windows Task Scheduler (a reminder on how to do this with a VB Script is here:
“Send en Email automatically with Excel and VBA integrating dynamic data from Reuters, Bloomberg, DataStream,…”)

Although a proper task scheduler can easily open a new instance of Excel, a new excel file and wait a particular amount of system free time (without system to be busy or running any background task) before launching a macro. But we’re staying on the cheapskate side using Windows Task Scheduler.

Ok, so what is this about? We are basically sending a message to the Twitter to tell to publish a status update. But if the message was not encrypted, anyone intercepting it could alter it. A huge part of the answer is situated on the following link. On common issues, Internet is magic, there’s always someone that have had the idea before you and who is kind enough to share it, the only work to do becomes a compilation of sources and tailoring to particular needs (all credits to those who deserve it, source and links always posted on that purpose):

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

The above link explains pretty well in the thread what is the principle of the encryption we’re looking for. Basically there’s a normal encryption systematic process: SHA1. The problem, if the data is intercepted by a malicious third part and the encryption method known, anyone can virtually intercept, process to a decryption, then modify the data, re-encrypt and sending it to achieve  malicious process. Adding a HMAC  is like adding an additional key to the encryption. If you are a malicious third party, if don’t know the key, it’s becoming more difficult to intercept and modify the data without being busted. When the recipient receives the data, if the data has been modified without the new computation key it will create a mismatch. Then it’s followed by a Base 64 encoding but, hey, should one understand the whole process, this would just a be a little detail by now!

The Base64 encryption method can be found there: http://stackoverflow.com/questions/169907/how-do-i-base64-encode-a-string-efficiently-using-excel-vba.

So it means, in our macro-enabled Excel file, we have to create 2 new functions in a VBA module and we can delete the whole basCryptoSys module:

Public Function Base64_HMACSHA1(ByVal sTextToHash As String, ByVal sSharedSecretKey As String)

    Dim asc As Object, enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

    TextToHash = asc.Getbytes_4(sTextToHash)
    SharedSecretKey = asc.Getbytes_4(sSharedSecretKey)
    enc.Key = SharedSecretKey

    Dim bytes() As Byte
    bytes = enc.ComputeHash_2((TextToHash))
    Base64_HMACSHA1 = EncodeBase64(bytes)
    Set asc = Nothing
    Set enc = Nothing

End Function

Private Function EncodeBase64(ByRef arrData() As Byte) As String

    Dim objXML As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement

    Set objXML = New MSXML2.DOMDocument

    ' byte array to base64
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.Text

    Set objNode = Nothing
    Set objXML = Nothing

End Function

Finally, the function calling the encryption, get_oauth_signature, has to be modified to call the new correct encryption functions:

Function get_oauth_signature(cBase, cKey)

    Dim abData() As Byte
    Dim abKey() As Byte
    Dim i As Integer
    Dim nDataLen As Long, nKeyLen As Long
    Dim strDigest As String * 40

    abKey() = StrConv(cKey, vbFromUnicode)
    nKeyLen = UBound(abKey) + 1
    abData() = StrConv(cBase, vbFromUnicode)
    nDataLen = UBound(abData) + 1
    

    nRet = Base64_HMACSHA1(cBase, cKey)

    Debug.Print 2; nRet; strDigest


    get_oauth_signature = URLEncode(((nRet)))

End Function

So here we are, we are now completely autonomous, the encryption don’t need a third party module. Free version! Is there any downside or disadvantage? Yes. However, at our level of requirement of usage and security, this is negligible. It’s about collision rate, more information is available there: http://searchsecurity.techtarget.com/answer/What-is-an-encryption-collision

But the most important is the following, quoting the above link:

But there’s an inescapable problem here. If we are creating a small fixed, length-hash value, say 128 bits, to represent any piece of data, large or small, it means that there are far more possible input values than there are unique hash values. Therefore more than one input stream can produce the same hash value. When this occurs, it is known as a collision. A hash function is deemed collision-resistant if it is hard to find two inputs that hash to the same output. Collision-resistant doesn’t mean that no collisions exist; simply that they are difficult to find.

Our method could be back tested but would return a collision rate of inbetween 0% and 0.5%. Out of 10,000 tweets, a maximum of 50 won’t be published. We can live with that.

Here is the updated Excel File to download, enjoy! AutoTwitter with Encryption.xlsm

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.