Currency Meter using ESP8266,Google Finance and Secure MQTT
Components used : ESP8266, MQTT Server with TLS, Google Spread Sheet, Python and OLED Display
Whilst planning to make a currency exchange tool, the first thing came to my mind was finding a Free currency converter API. I used to develop some desktop applications for currency conversion few years ago and I was having the perception that API implementation in ESP8266 would be pretty straight forward using HTTP libraries and REST calls. But I could not find any free API for this purpose, all of the API providers imposed limitations on their API call. Many of the old providers like Yahoo stopped providing APIs and others made it a paid service with monthly or yearly subscriptions.
On continuing the search, I read somewhere that Google spread sheet offers an an inbuilt function call GOOGLEFINANCE() where we can give two currency symbols to get the conversion rate without any limitation. My next step was to find a way to fetch the values from the Google Spread sheet cells and pass to MQTT server, that can be subscribed to ESP8266 using mqtt libraries.
Following the link https://developers.google.com/sheets/api/quickstart/python, I could manage to read the Google spread sheet cell values and publish it to my MQTT server using the below code and get the desired result.
# Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
range=SAMPLE_RANGE_NAME).execute()
values = result.get('values', [])
if not values:
print('No data found.')
else:
for row in values:
# Print columns A and E, which correspond to indices 0 and 4.
#print('%s : %.3f' % (row[0], float(row[1])))
bhd_string = '%s : %.3f' % (row[0], float(row[1]))
usd_string = '%s : %.3f' % (row[2], float(row[3]))
sar_string = '%s : %.3f' % (row[4], float(row[5]))
aed_string = '%s : %.3f' % (row[6], float(row[7]))
cmd = "mosquitto_pub -p 1883 -t \"Curr-001\" --cafile /etc/iot/googlefinance/ca.crt -m \"{0}QQ{1}QQ{2}QQ{3}\" -h xyz.myhost.com".format(bhd_string,usd_string,sar_string,aed_string)
os.system(cmd)
I have created a crontab entry to run the script every 1 minute to send the currency updates in every minutes.
* * * * * /etc/iot/googlefinance/currency.py
OLED Display SSD1306 Interfacing
The OLED display uses I2C communication protocol and wiring is so simple
In the ESP8266 Sketch, I subscribed to the TOPIC where the currency values are sent in a callback function.
ESP8266 Sketch
#include <SPI.h>
#include <Wire.h>
#include <Adafruit_GFX.h>
#include <Adafruit_SSD1306.h>
#include <ESP8266WiFi.h>
#include <PubSubClient.h>
#define ESP8266_LED (5)
#define SERIAL_DEBUG
#define TLS_DEBUG
#define OLED_RESET LED_BUILTIN //4
Adafruit_SSD1306 display(OLED_RESET);
WiFiClientSecure espClient;
PubSubClient mqttClient(espClient); /* MQTT Client connection */
String clientId = "ESP8266Client-"; /* MQTT client ID (will add random hex suffix during setup) */
/* CA Cert in PEM format */
const char caCert[] PROGMEM = R"EOF(
-----BEGIN CERTIFICATE-----
MIIDsjCCApoCCQDdBbUe4hJN+zANBgkqhkiG9w0BAQsFADCBmTELMAkGA1UEBhMC
QkgxDzANBgNVBAgMBk1hbmFtYTEPMA0GA1UEBwwGTWFuYW1hMRIwEAYDVQQKDAlH
cmFzc3BhbG0xFjAUBgNVBAsMDUdyYXNzcGFsbS1JT1QxGTAXBgNVBAMMEGdiLmdy
4nUCCZ8iZaGTB4XdaZMFXCWlWipjkb+QFnMgdtSK5oSxUqJQ/v7TF/OF+FDi9VI8
o696VzrNkbAvDfA5vOiiXSVevaol3nQCANeFIq72qG2ZpQA04f6Qacbwvx9NAgMB
AAEwDQYJKoZIhvcNAQELBQADggEBAC+vI65BKPDrgMXaRebQTcdUiPtK+3EJVXVg
zw1kZ/rkAVn8cPUKK6xf3+6L+c7m06OQOvgwXA3VN3d/dc4nZuM/iZufObDWp8w0
j+m9n51cARVsRzEIdQSht4yVxAwg2KMB8a1X3hKtFljOH0GKj2N557U5bNNQ++5n
DYwgx2x3yEhEhNU2qTmCPsSlQEYRxRgj9aAP6mY1wdGTVnqfop9kIrFnY8uRdBLs
u83Eaf/lPlasmLeQUtp+PcMA6LcFgjJQS9Eai2KPyOLJq4fiD0gwKiVyUL59NDWk
hT7QcVOFigVj0nm9QdO9KbTO2dIIymCmr0sXDer3Z3GeL8ZwWbk=
-----END CERTIFICATE-----
)EOF";
/* MQTT broker cert SHA1 fingerprint, used to validate connection to right server */
const uint8_t mqttCertFingerprint[] = {0x61,0x5B,0x5A,0xB9,0xAF,0x36,0x79,0xE3,0x21,0x09,0x04,0x9C, 0xC2,0x32,0x93,0x6C,0x57,0xCC,0x8C,0xF4};
/* Other globals */
X509List caCertX509(caCert); /* X.509 parsed CA Cert */
//WiFiClientSecure espClient; /* Secure client connection class, as opposed to WiFiClient */
#ifdef TLS_DEBUG
/* verifytls()
* Test WiFiClientSecure connection using supplied cert and fingerprint
*/
bool verifytls() {
bool success = false;
#ifdef SERIAL_DEBUG
Serial.print("Verifying TLS connection to ");
Serial.println("xyz.abc.com");
#endif
success = espClient.connect("xyz.abc.com", 1883);
#ifdef SERIAL_DEBUG
if (success) {
Serial.println("Connection complete, valid cert, valid fingerprint.");
}
else {
Serial.println("Connection failed!");
}
#endif
return (success);
}
#endif
void reconnect() {
/* Loop until we're reconnected */
while (!mqttClient.connected()) {
#ifdef SERIAL_DEBUG
Serial.print("Attempting MQTT broker connection...");
#endif
/* Attempt to connect */
if (mqttClient.connect(clientId.c_str())) {
#ifdef SERIAL_DEBUG
Serial.println("connected");
#endif
/* Once connected, resubscribe */
mqttClient.subscribe("Fin-001");
}
else {
#ifdef SERIAL_DEBUG
Serial.print("Failed, rc=");
Serial.print(mqttClient.state());
Serial.println(". Trying again in 5 seconds...");
#endif
/* Wait 5 seconds between retries */
delay(5000);
}
}
}
void setup()
{
display.begin(SSD1306_SWITCHCAPVCC, 0x3C);
display.clearDisplay();
display.display();
#ifdef SERIAL_DEBUG
/* Initialize serial output for debug */
Serial.setDebugOutput(true);
Serial.begin(9600, SERIAL_8N1);
Serial.println();
#endif
/* Connect to local WiFi access point */
WiFi.mode(WIFI_STA);
WiFi.begin("mywifi", "wifipassword");
#ifdef SERIAL_DEBUG
Serial.print("Connecting");
display.clearDisplay();
display.display();
display.setTextSize(2);
display.setTextColor(WHITE);
display.setCursor(0,0);
#endif
while (WiFi.status() != WL_CONNECTED)
{
delay(500);
#ifdef SERIAL_DEBUG
Serial.print(".");
display.print(".");
display.display();
#endif
}
#ifdef SERIAL_DEBUG
/* When WiFi connection is complete, debug log connection info */
Serial.println();
Serial.print("Connected, IP address: ");
Serial.println(WiFi.localIP());
display.clearDisplay();
display.setTextSize(1);
display.setTextColor(WHITE);
display.setCursor(0,0);
display.println("::Currency Meter::");
display.println("__________________");
display.println("IPv4 Address");
display.println(WiFi.localIP());
display.display();
delay(3000);
#endif
espClient.setTrustAnchors(&caCertX509); /* Load CA cert into trust store */
espClient.allowSelfSignedCerts(); /* Enable self-signed cert support */
espClient.setFingerprint(mqttCertFingerprint); /* Load SHA1 mqtt cert fingerprint for connection validation */
#ifdef TLS_DEBUG
/* Call verifytls to verify connection can be done securely and validated - this is optional but was useful during debug */
verifytls();
#endif
/* Configure MQTT Broker settings */
mqttClient.setServer("gb.grasspalm.com",8883);
mqttClient.setCallback(subCallback);
/* Add random hex client ID suffix once during each reboot */
clientId += String(random(0xffff), HEX);
}
void loop()
{
/* Main loop. Attempt to re-connect to MQTT broker if connection drops, and service the mqttClient task. */
if(!mqttClient.connected()) {
reconnect();
}
mqttClient.loop();
}
void subCallback(char *topic, byte *payload, unsigned int length)
{
Serial.println("Received......");
if (topic=="Curr-001");
{
String recv_payload = String(( char *) payload);
recv_payload.replace("QQ", "\n");
Serial.println(recv_payload.substring(0,length));
display.clearDisplay();
display.display();
display.setTextSize(2);
display.setTextColor(WHITE);
display.setCursor(10,10);
display.println("Loading...");
display.display();
delay(1000);
display.clearDisplay();
display.display();
display.setTextSize(1);
display.setTextColor(WHITE);
display.setCursor(0,0);
display.println(recv_payload.substring(0,length));
display.display();
delay(1000);
}
}
