Hello Fellow developers !

As mentionned on @cocaaladioxine/so-many-projects-so-little, I have a basic voting bot that relies on a ML (machine learning) Model to predict the final value of the posts.
I know there's a lot of mistakes in the way I implemented it as I still have many things to learn about Hive, and especially the way rewards works.

Anyway, this little project is a good practicing playground for various technologies I don't use (anymore) on a daily basis.

My ML Model is not very precise and uses the total hbd reward found on the comments table in the HiveSQL database.

I just thought that this value was much probably impacted by the Hive/HBD exchange rate as the reward is based on the Hive Power of each voter. Maybe I'm wrong and I would be glad if someone wanted to mythbust this idea. Still, I decided to test it and see if I could reach a better score with my model.

In order to do so, I need to have a way to convert past HDB to Hives. I had a look at the Beem python library but could not find what I was looking for. So I decided to build my own.

Finding historical data

I found daily historical value of HBD/USD and HIVE/USD on different websites, but was unable to find a HIVE/HBD ticker, with history.
I decided to go for the coingecko data, which has a daily granularity. It's enough for me, and we can even download the data as a CSV.

You'll find the data here :

• HBD/USD on coingecko
• HIVE/USD on coingecko

The HBD/USD variation is small, but it's not really a challenge to compute the real HBD/HIVE using HBD/USD + HIVE/USD. Let's do it then !

Preparing the Database

I use a MariaDB database on my Raspberry pi 3. It's more than enough for my use cases.
We connect to the database and create the tables :

CREATE TABLE crypto.hbd_usd_ticker (
    value_date DATE NOT NULL,
    price DOUBLE NULL,
    market_cap DOUBLE NULL,
    total_volume DOUBLE NULL,
    PRIMARY KEY (value_date));

CREATE TABLE crypto.hive_usd_ticker (
    value_date DATE NOT NULL,
    price DOUBLE NULL,
    market_cap DOUBLE NULL,
    total_volume DOUBLE NULL,
    PRIMARY KEY (value_date));

Those 2 tables will hold the data respectively for HBD and HIVE. Nothing fancy here

Loading the data

The mariadb/mysql command to load a file is quite easy and is executed in the mariadb CLI:

LOAD DATA INFILE '/tmp/HBD-usd-max.csv' 
INTO TABLE hbd_usd_ticker 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

LOAD DATA INFILE '/tmp/HIVE-usd-max.csv' 
INTO TABLE hive_usd_ticker 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

There is a header in the files, so we ignore the 1st row. It's a real CSV with a , separator. The syntax is quite easy to read.
It's easier to put the file in the tmp directory as it's usually readable by anyone. Don't forget to give read rights on your files with a little

$ chmod +r *.csv

Computing the HIVE/HBD historical values

First we create the table to hold the historical values. I don't need the market cap and volume so we'll drop it. I thought it would be practical to have both the hbd per hive AND hive per hbd conversion, as it would be clearer in the code than using a division or a multiplication.

CREATE TABLE crypto.hive_hbd_daily_ticker (
  value_date DATE NOT NULL,
  hbd_per_hive DECIMAL(8,4) NULL,
  hive_per_hbd DECIMAL(8,4) NULL,
  PRIMARY KEY (value_date)); 

I also changed the datatype from double to decimal for personal preferences (I know in Teradata it would take less storage than a double, but I'm not a MySQL expert). As I don't need much precision, 4 decimals are more than enough.
Hive value can now range from 9999.9999$ to 0.0001$ (the former better than the latter ^^ ).

It's now easy to fill the table :

INSERT INTO crypto.hive_hbd_daily_ticker
SELECT 
  hive.value_date,
  round(hive.price/hbd.price, 4) as hbd_per_hive,
  round(hbd.price/hive.price, 4) as hive_per_hbd
FROM crypto.hive_usd_ticker hive
INNER JOIN crypto.hbd_usd_ticker as hbd on 1=1 
    and hive.value_date = hbd.value_date ;

And then ? What about tomorrow's value ?

Yup, that's nice, we have historical data...
But storing it everyday without having to import files would be great.
I wrote a little python script to do it. It's simple and I'm not a python expert so it's probably not "nice coding", but it works !

import configparser
from beem.market import Market
import mariadb as sql

# Read the configuration file 
config = configparser.ConfigParser()
config.read('../conf.ini')

# Get the last Market value
m = Market('HIVE', 'HBD')
tick = m.ticker()['latest']

# Round the data
hbd_per_hive = round(tick['price'], 4)
hive_per_hbd = round(1/tick['price'],4)

# Connect to the DB and init cursor 
db_connection = sql.connect(
        host=config['db']['Host'], 
        database=config['db']['Database'], 
        user=config['db']['User'], 
        password=config['db']['Password'],
        autocommit=True)
cursor = db_connection.cursor()

# If it runs multiple times a day
delete_sql = 'delete from crypto.hive_hbd_daily_ticker where value_date = current_date()  ;'

insert_sql = (
    "insert into crypto.hive_hbd_daily_ticker (value_date, hbd_per_hive, hive_per_hbd)" 
    " VALUES ( current_date(), %s , %s);"
)
 
# Delete today, Insert current
cursor.execute(delete_sql)
cursor.execute(insert_sql, (hbd_per_hive, hive_per_hbd))
cursor.close()
db_connection.close()

That's it, I added some comments but the code should be easy to read as there is nothing out of the ordinary.

Here's the beem's Market documentation

Conclusion

I added this script to my crontab and decided to make it run every hour. It's not mandatory as I only want the value at the end of the day, but the raspberry is subject to crashes, and does not have an uninterruptible power supply. So it's almost guaranteed that I'll have a complete history even if something happens during the day.

I'll update my ML code to see if there's an improvement !

If someone has some tricks to have a nicer code formatting, I'll gladly take them !

See you !