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):
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:
Finally, the function calling the encryption, get_oauth_signature, has to be modified to call the new correct encryption functions:
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