Introduction

Currensee is an open-source project that uses the MindsDB Python SDK to query machine learning models and render blockchain predictions on the front end. Programmed in Python, Flask, HTML5, CSS3, JavaScript, and Bootstrap 5, Currensee was created for the #MindsDB April 2023 Hackathon here on @Hashnode.

The steps in this article demonstrate the steps I took to create time-series machine-learning models and bring them into this project. Building this has been an incredible learning experience, and I hope it inspires others to get started with @MindsDB.

Ideation

When I started ideating this project a lot of use cases came to mind as I reflected on my experience contributing to MindsDB in prior hackathons. In general, I tend to shy away from building projects that require new & unfamiliar languages, technologies, and configurations when I participate in hackathons or competitions. I do this so I can avoid complications, ensure everything looks great on the front end... and, well, because I like to win.

While this method of mine does certainly lead to a lot of "first place" awards, it also prevents me from gaining new knowledge. The way I see it, I should learn new things if I'm going to participate in these events and I should help others learn too. I was leaning towards an educational program that would use MindsDB & ChatGPT when it occurred to me - I never try to tackle tasks related to time series or time regression.

That said, I chose a use case that was new, interesting and challenging to me:

Use historical blockchain data to train time-series machine-learning models

Project Goals

The main goal for Currensee was originally this simple:

- Display coin prediction data on the front end by connecting to MindsDB machine-learning models

However, once I got my hands dirty with MindsDB's Python SDK another goal quickly formed:

- Provide an open-source project where developers & data scientists can learn how to use Python to connect to their MindsDB instances

Features

Currensee currently uses the MindsDB Python SDK to connect to MindsDB Cloud and run machine learning queries on pre-trained predictor models.

The backend setup currently allows users to fetch predictions for open, close, high and low prices for the next 14 days by clicking on the coin of their choice. When the user clicks on a coin, it triggers a request to MindsDB's server, instructing it to query the coin's corresponding prediction model with the query specified in app.py. The prediction data is then displayed on a dedicated page:

The prediction models that are being queried were pre-trained on these datasets, which contain daily prices of Bitcoin & Ethereum from 2014 to April 2023.

This build of Currensee does not technically need a Python/Flask backend to communicate with MindsDB, but this configuration will be kept in place for use in feature releases.

Development

The rest of this article is written in a second-person, tutorial style to encourage developers & data scientists to follow along.

Data Setup

If you want to follow along with this article, head over to Kaggle and download the Bitcoin & Ethereum files included in this dataset. Create Database.

ADD DATA

The first step is to upload your dataset to MindsDB's Cloud Editor, which will instantly turn the CSV file into a table. This table lives inside the existing files database, which is built in the MindsDB Cloud Editor.

In the event you were working inside other databases, go ahead and type: use mindsdb;

Find the 'Add' dropdown button above the text editor and select, 'Upload File'.

Click on the Import a File input field and select the dataset you chose in the previous section.

Name your data source, making sure to use _ instead of spaces. Then, click 'Save and Continue'.

After you click 'Save and Continue', you will be redirected to the text editor where you should see output similar to this:

Use the SHOW TABLES statement to list all the tables inside the files database. Make sure the table you just created is there:

Use SQL SELECT to print 10 rows from the dataset to confirm the data was imported successfully:
```
  SELECT * FROM files.btc_daily
  LIMIT 10;
```
If you want to be extra careful, use DESC to print the last 10 rows of the dataset and ensure all the data is present in your table:

SELECT * FROM files.btc_daily
ORDER BY Date DESC
LIMIT 10;

CREATE MODEL

Case Study: Ethereum Model

Start by creating the model:

CREATE MODEL eth_model -- eth_model is the name of the model being created
FROM files ( -- files is the database
  SELECT * FROM Ethereum -- Ethereum is the file name
  )
PREDICT Close -- target column
ORDER BY Date -- order by the date for time-series best practice
WINDOW 360  -- model will train on the last 360 rows of historical data from the CSV file
HORIZON 14; -- model will predict next 14 records (or 2 weeeks)

Use SELECT to check the status:

SELECT * 
FROM mindsdb.models 
WHERE name='eth_model';

On return, you should see that your model is finished training. When I ran these queries, the accuracy was .984 right after the model was created.

| NAME | ENGINE | PROJECT | VERSION | STATUS | ACCURACY | PREDICT | UPDATE_STATUS | MINDSDB_VERSION | ERROR | SELECT_DATA_QUERY | TRAINING_OPTIONS | CURRENT_TRAINING_PHASE | TOTAL_TRAINING_PHASES | TRAINING_PHASE_NAME | TAG | CREATED_AT |
| ---- | ------ | ------- | ------- | ------ | -------- | ------- | ------------- | --------------- | ----- | ----------------- | ---------------- | ---------------------- | --------------------- | ------------------- | --- | ---------- |
| eth_model | lightwood | mindsdb | 1 | complete | 0.984 | Close | up_to_date | 23.4.4.2 | [NULL] | SELECT * FROM Ethereum | {'target': 'Close', 'timeseries_settings': {'is_timeseries': True, 'order_by': 'Date', 'horizon': 14, 'window': 360}} | 5 | 5 | Complete | [NULL] | 2023-04-29 20:20:45.814334 |

JOIN the dataset with the predictor model.

SELECT T.Date as Date,
T.Close as Prediction, Close_explain
FROM mindsdb.eth_model as T
JOIN files.Ethereum as P
WHERE P.Date > LATEST
LIMIT 14;

If all goes well, you should see close price predictions for the next two weeks after the last record in the Ethereum dataset.

| Date | Prediction | Close_explain |
| ---- | ---------- | ------------- |
| 2023-04-22 00:00:00.000000 | 1906.5461969333821 | {"predicted_value": 1906.5461969333821, "confidence": 0.99, "anomaly": false, "truth": null, "confidence_lower_bound": 1895.8956342233894, "confidence_upper_bound": 1917.196759643375} |
| 2023-04-23 00:00:00.000000 | 1906.4088652388039 | {"predicted_value": 1906.4088652388039, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1892.7700387674759, "confidence_upper_bound": 1920.0476917101319} |
| 2023-04-24 00:00:00.000000 | 1904.1334034746387 | {"predicted_value": 1904.1334034746387, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1883.8630388972417, "confidence_upper_bound": 1924.4037680520357} |
| 2023-04-25 00:00:00.000000 | 1904.968907912788 | {"predicted_value": 1904.968907912788, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1881.7761966553091, "confidence_upper_bound": 1928.1616191702667} |
| 2023-04-26 00:00:00.000000 | 1905.0470105833956 | {"predicted_value": 1905.0470105833956, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1879.8729528527074, "confidence_upper_bound": 1930.2210683140838} |
| 2023-04-27 00:00:00.000000 | 1898.7874470684508 | {"predicted_value": 1898.7874470684508, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1861.0100696988736, "confidence_upper_bound": 1936.564824438028} |
| 2023-04-28 00:00:00.000000 | 1892.3545938629816 | {"predicted_value": 1892.3545938629816, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1852.2839175228708, "confidence_upper_bound": 1932.4252702030924} |
| 2023-04-29 00:00:00.000000 | 1892.0447755735597 | {"predicted_value": 1892.0447755735597, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1850.4100801222146, "confidence_upper_bound": 1933.6794710249048} |
| 2023-04-30 00:00:00.000000 | 1891.892715355531 | {"predicted_value": 1891.892715355531, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1848.8760074450395, "confidence_upper_bound": 1934.9094232660225} |
| 2023-05-01 00:00:00.000000 | 1889.6187912859514 | {"predicted_value": 1889.6187912859514, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1826.176991987458, "confidence_upper_bound": 1953.0605905844448} |
| 2023-05-02 00:00:00.000000 | 1890.4320969993503 | {"predicted_value": 1890.4320969993503, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1825.6546319949864, "confidence_upper_bound": 1955.2095620037142} |
| 2023-05-03 00:00:00.000000 | 1890.4937385306282 | {"predicted_value": 1890.4937385306282, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1822.1843522152562, "confidence_upper_bound": 1958.8031248460002} |
| 2023-05-04 00:00:00.000000 | 1884.2661462907984 | {"predicted_value": 1884.2661462907984, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1815.8515146831949, "confidence_upper_bound": 1952.6807778984019} |
| 2023-05-05 00:00:00.000000 | 1877.8666610075659 | {"predicted_value": 1877.8666610075659, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 1809.271158103782, "confidence_upper_bound": 1946.4621639113498} |

Great job so far. Time series models can be challenging, so don't get discouraged if you have some trouble or run into some errors.

Bringing MindsDB Models To Currensee

Once the database is set up and the predictor is trained, there has to be a way to bring the prediction data to the web application so it can be displayed in HTML.

Import MindsDB at the top of .app.py

import mindsdb_sdk
from config import Config
from settings import MINDSDB_EMAIL, MINDSDB_PASSWORD


@app.route('/')
def index():
    return render_template('index.html')

Define the Cloud server instance if you plan on connecting this way(note: I have all of my sensitive credentials in an external file. You will have to set this up on your end)

import mindsdb_sdk
from config import Config
from settings import MINDSDB_EMAIL, MINDSDB_PASSWORD

mdb = mindsdb_sdk.connect('https://cloud.mindsdb.com', email=MINDSDB_EMAIL, password=MINDSDB_PASSWORD) # your actual username and password will have to be passed here

Now that you've initiated the connection to the server, create a view to display the predictions you just made on the front end using HTML.

@app.route('/ethereum', methods=['GET', 'POST'])
def ethereum():
    """
    Method to return ethereum prediction data when a user visits     ethereum.html
    """
    server = mindsdb_sdk.connect('https://cloud.mindsdb.com', email=MINDSDB_EMAIL, password=MINDSDB_PASSWORD)
    project = server.get_project('mindsdb')
    query = project.query('SELECT T.Date as Date, T.Close as Prediction, Close_explain FROM mindsdb.eth_1 as T JOIN files.Ethereum as P WHERE P.Date > LATEST LIMIT 7;')
    eth_df = DataFrame.to_html(query.fetch(), index=False)

    return render_template('ethereum.html', query=query, eth_df=eth_df)

You will need to install & import pandas to make use of DataFrame and to_html()

pip install pandas

Then, at the top of app.py:

import pandas as pd
from pandas import DataFrame

Next Steps

Now that the first release is up and running, there's a lot to be done!

Some of the main tasks I'm busy with right now include:

Compile CONTRIBUTING.md.
Build & connect a PostgreSQL, Mongo or SQLAlchemy database to hold user and coin models.
Automate the process of retraining models
Add authentication
Write an action (.yml) to verify commit signs.
Write an action (.yml) to update Kaggle datasets monthly.
Visualize the predictions on the front-end using MindsDB and a MindsDB integration
Automated the process of adding coin card(s) to the front-end (ways this can be implemented: use jinja for loop to automatically create a new card when the user submits the add coin form - this can also be done with JSON data)

Conclusion

If you're still following along you're probably interested in contributing to Currensee! Head over to GitHub, fork this project, create a branch, and get started! When you're ready, open a pull request. Don't be shy, I'd love some extra hands on this!

#mindsdb @mindsdb @hashnode #machinelearning #AI #datascience #mindsdbhackathon

Deep Dives