Summary

The app relied on a python script hosted on AWS that made a request to the Binance.com API, and obtain ticker values every 60 seconds. Those ticker values were subsequently forwarded to an SQLite database from which a Heroku-based app could draw data and generate plots.

A notable bug we encountered was that the SQL database was getting about 30 duplicate values from Binance each minute. This led to our database filling up beyond the capacity alloted to a free account, which meant we needed to discontinue the app after only 2 months instead of letting it continue to collect data. It is because of this quirk that we needed to add logic to the app.py file so that the Flask route would take only the single value of interest before dumping it into the json object that would then be used by the javascript file to dynamically render the plot.

Also, an early rendition of the application did not present the data in a plotly-friendly format. So, also included as a bonus at the bottom of the page is the logic I used to clean the data on the front-end with Javascript. It was not the fastest solution, but given my limited access, it did solve the problem at hand admirably.

Unfortunately, the Heroku link that housed this app longer works, and there are no screenshots of the functional app. The Python script hosted on AWS that got the ticker values every 60 seconds has also been lost also. You'll have to just take my word for it: It was a cool, functional app.


Solution


App.py File



        from flask import (
            Flask, 
            jsonify, 
            render_template, 
            request, 
            redirect)
        import json
        import pandas as pd 
        from flask_sqlalchemy import SQLAlchemy
        
        app = Flask(__name__, template_folder="templates")
        #Set Squalchemy database
        app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db/CryptoData.sqlite'
        #make a db reference
        db = SQLAlchemy(app)
        
        #create a empty database Model of USD/BTC
        class CryptoData(db.Model):
            __tablename__ = 'CryptoTable'
        
            id = db.Column(db.Integer, primary_key=True)
            # Currency = db.Column(db.String(64))
            Date = db.Column(db.String(64))
            Close = db.Column(db.Integer)
        
            def __repr__(self):
                return '' % (self.Currency)
            # CryptoData.query.all()
        
        # #Create database tables
        @app.before_first_request
        def setup():
        #     Recreate database each time for demo
        #     db.drop_all()
            db.create_all()
        
        file = "static/data/historical_data_final.csv"
        historical_df = pd.read_csv(file)
        
        currency_list = {
                "XRP" : 1,
                "ETH" : 2, 
                "LTC" : 3, 
                "BCC" : 4, 
                "EOS" : 5, 
                "BNC" : 6
                }   
        
        # SAMPLE DATA TO TEST 
        live_data = "static/data/sampledata.csv"
        live_df = pd.read_csv(live_data)
        
        @app.route("/")
        def index():
            return render_template('index.html')
        
        @app.route("/explore")
        def explore():
            return render_template('explore.html')
            
        @app.route("/history")
        def history():
            return render_template('history.html')
        
        @app.route("/comparison")
        def comparison():
            return render_template('comparison.html')
        
        
        '''
        The following routes are for calling two distinct BTC API for building a 
        correlation plot. 
        '''
        
        @app.route("/live_data_trace1/")
        def live_data(firstBTC):
            first_live_df = live_df.loc[live_df["symbol"] == firstBTC]
        
            first_live_data = {
                "id": first_live_df["id"].tolist(), 
                "price": first_live_df["price"].tolist(),
                "symbol": first_live_df["symbol"].tolist(),
                "crypto_timestamp": first_live_df["crypto_timestamp"].tolist()
            }
        
            return jsonify(first_live_data)
        
        @app.route("/live_data_trace2/")
        def second_data(secondBTC):
            second_live_df = live_df.loc[live_df["symbol"] == secondBTC]
        
            second_live_data = {
                "id": second_live_df["id"].tolist(), 
                "price": second_live_df["price"].tolist(),
                "symbol": second_live_df["symbol"].tolist(),
                "crypto_timestamp": second_live_df["crypto_timestamp"].tolist()
            }
        
            return jsonify(second_live_data)
        
        
        '''
        The following routes are for calling the historical data API 
        of each ETC and plotting the data in JS.
        '''
        
        @app.route("/BTC")
        def names():
            """Return a list of BTC names."""
            return (jsonify(list(currency_list)))
            
        @app.route("/historical_data/")
        def historical_data(correctedCurrency):
            currency_data = historical_df.loc[historical_df["correctedCurrency"] == correctedCurrency]
        
            data = {
                "Date": currency_data["Date"].tolist(),
                "Close": currency_data["Close"].tolist(),
            }
        
            return jsonify(data)
        #put flask data into element "plot"
        # Query the database and send the jsonified results
        @app.route("/plot_USDBTC")
        def USDBTC_data():
            # print("hello")
            #Query for the USDBTC data using pandas
            results = db.session.query(CryptoData.Date, CryptoData.Close).order_by(CryptoData.Date.desc()).all()
            # print(results)
            Date = [result[0] for result in results]
            Price = [result[1] for result in results]
            # print(Date, Price)
        
        #(change array to list and set the x and y data)
        
        #Directory change into plot trace
            trace = {
                "x": Date,
                "y": Price,
                "type": "plot"
            }
        
            return jsonify(trace)
        
        
        if __name__ == "__main__":
            app.run(debug=True)
        

Final Javascript File



            console.log("Script is here");

            // /** 
            // * @param {array} rows
            // * @param {integer} index
            
            // */
            var tbody = d3.select("tbody");
            var row = tbody.append("tr");
            var cell = tbody.append("td");
            
            function unpack(rows, index) {
                return rows.map(function(row) {
                  return row[index];
                });
              };
            
            function getPearsonCorrelation(x, y) {
              var shortestArrayLength = 0;
                
              if(x.length == y.length) {
                  shortestArrayLength = x.length;
              } else if(x.length > y.length) {
                  shortestArrayLength = y.length;
                  console.error('x has more items in it, the last ' + (x.length - shortestArrayLength) + ' item(s) will be ignored');
              } else {
                  shortestArrayLength = x.length;
                  console.error('y has more items in it, the last ' + (y.length - shortestArrayLength) + ' item(s) will be ignored');
              }
            
              var xy = [];
              var x2 = [];
              var y2 = [];
            
              for(var i=0; i< shortestArrayLength; i++) {
                  xy.push(x[i] * y[i]);
                  x2.push(x[i] * x[i]);
                  y2.push(y[i] * y[i]);
              }
            
              var sum_x = 0;
              var sum_y = 0;
              var sum_xy = 0;
              var sum_x2 = 0;
              var sum_y2 = 0;
            
              for(var i=0; i< shortestArrayLength; i++) {
                  sum_x += x[i];
                  sum_y += y[i];
                  sum_xy += xy[i];
                  sum_x2 += x2[i];
                  sum_y2 += y2[i];
              }
            
              var step1 = (shortestArrayLength * sum_xy) - (sum_x * sum_y);
              var step2 = (shortestArrayLength * sum_x2) - (sum_x * sum_x);
              var step3 = (shortestArrayLength * sum_y2) - (sum_y * sum_y);
              var step4 = Math.sqrt(step2 * step3);
              var answer = step1 / step4;
            
              console.log(answer);
              var row = tbody.append("tr");
              var cell = tbody.append("td");
              cell.text(symbol1Bro);
              var cell = tbody.append("td");
              cell.text(symbol2Bro);
              var cell = tbody.append("td");
              cell.text(datetimeBro);
              var cell = tbody.append("td");
              cell.text(shortestArrayLength);
              var cell = tbody.append("td");
              cell.text(answer);
              row.text("");
            };
            
            
            function respond_to_button() {
                d3.event.preventDefault();
                console.log("Your button is reacting");
                var userSelectedCrypto1 = d3.select("#firstCurrency").node().value;
                var userSelectedCrypto2 = d3.select("#secondCurrency").node().value;
                var userSelectedDateTime1 = d3.select("#firstDateTime").node().value;
                var userSelectedDateTime2 = d3.select("#secondDateTime").node().value;
                console.log(userSelectedCrypto1);
                console.log(userSelectedDateTime1);
                datetimeBro = String(userSelectedDateTime1);
                symbol1Bro = String(userSelectedCrypto1).slice(0,3);
                symbol2Bro = String(userSelectedCrypto2).slice(0,3);
                buildPlot(userSelectedCrypto1, userSelectedCrypto2, userSelectedDateTime1, userSelectedDateTime2);
            };
            
            function buildPlot(userSelectedCrypto1, userSelectedCrypto2, userSelectedDateTime1, userSelectedDateTime2) {
            
                // URL to help resolve CORS issue 
                var proxyurl = "https://cors-anywhere.herokuapp.com/";
            
                var corr_data_fetch_url = `https://flask-crypto.herokuapp.com/livedata/${userSelectedCrypto1}/${userSelectedCrypto2}/${userSelectedDateTime1}/${userSelectedDateTime2}`;
            
                fetch(proxyurl + corr_data_fetch_url).then(function(response) {
                    console.log(response);
                    return response.json();
                    }).then(function(data) {
            
                    // Grab values from the response json object to build the plots
                    var symbol = unpack(data.dataset.data, 0);
                    var price = unpack(data.dataset.data, 1);
                    var timestamp = unpack(data.dataset.data, 2);
            
                    // console.log(symbol);
                    // console.log(price);
                    // console.log(timestamp);
            
            
            
                    var halfWayThrough = Math.floor(timestamp.length / 2)
            
                    var symbolBrah = symbol.slice(0, halfWayThrough);
                    var symbolBrah2 = symbol.slice(halfWayThrough, symbol.length);
                    var priceBrah = price.slice(0, halfWayThrough);
                    var priceBrah2 = price.slice(halfWayThrough, price.length);
                    var datetimeBrah = timestamp.slice(0, halfWayThrough);
            
            
                    minutesInt = Array.from(Array(datetimeBrah.length).keys())
                    // console.log(minutesInt)
            
                  console.log(symbolBrah)
                  console.log(priceBrah);
                  console.log(symbolBrah2);
                  console.log(priceBrah2);
            
                    var trace1 = {
                        x: minutesInt,
                        y: priceBrah,
                        name: symbol1Bro,
                        type: 'scatter'
                      };
                      var trace2 = {
                        x: minutesInt,
                        y: priceBrah2,
                        xaxis: 'x2',
                        yaxis: 'y2',
                        name: symbol2Bro,
                        type: 'scatter'
                      };
                      
                      var data1 = [trace1, trace2];
                      
                      trace3 = {
                        x: priceBrah,
                        y: priceBrah2,
                        name: `${userSelectedCrypto1} & ${userSelectedCrypto2}`,
                        type: 'scatter',
                        mode: 'markers'
                      }
                      
                      var data2 = [trace3];
                      
                    var trace4 = {
                        y: priceBrah,
                        name: symbol1Bro,
                        type: "box"
                    };
                    var trace5 = {
                        y: priceBrah2,
                        name: symbol2Bro,
                        type: "box"
                    };
            
                    var data3 = [trace4];
                    var data4 = [trace5];
                      
                    var layout = {
                      grid: {rows: 1, columns: 2, pattern: 'independent'},
                      xaxis: {
                            title: {
                              text: 'Minutes',
                              font: {
                                family: 'Courier New, monospace',
                                size: 18,
                                color: '#7f7f7f'
                              }
                            },
                          },
                    };
                      // var layout = {
                      //   xaxis: {
                      //     title: {
                      //       text: 'Minutes',
                      //       font: {
                      //         family: 'Courier New, monospace',
                      //         size: 18,
                      //         color: '#7f7f7f'
                      //       }
                      //     },
                      //   },
                      //   yaxis: {
                      //     title: {
                      //       text: `${userSelectedCrypto1} exchange rates`,
                      //       }
                      //     },
                      //   yaxis2: {
                      //     title: {
                      //       text: `${userSelectedCrypto2} exchange rates`,
                      //       titlefont: {color: 'rgb(148, 103, 189)'},
                      //       tickfont: {color: 'rgb(148, 103, 189)'},
                      //       overlaying: 'y',
                      //       side: 'left'
                      //     }
                      //   }
                      //   };
                      
            
                      var layout2 = {
                        // title: {
                        //   text:'Scatterplot',
                        //   font: {
                        //     family: 'Courier New, monospace',
                        //     size: 24
                        //   },
                        //   xref: 'paper',
                        //   x: 0.05,
                        // },
                        xaxis: {
                          title: {
                            text: symbol1Bro,
                            font: {
                              family: 'Courier New, monospace',
                              size: 18,
                              color: '#7f7f7f'
                            }
                          },
                        },
                        yaxis: {
                          title: {
                            text: symbol2Bro,
                            font: {
                              family: 'Courier New, monospace',
                              size: 18,
                              color: '#7f7f7f'
                            }
                          }
                        }
                      };
            
                      var layout3 = {
                        title: {
                          text: symbol1Bro,
                          font: {
                            family: 'Courier New, monospace',
                            size: 24
                          },
                          xaxis: ''
                        }
                      };
                      var layout4 = {
                          title: {
                            text: symbol2Bro,
                            font: {
                              family: 'Courier New, monospace',
                              size: 24
                            },
                            xaxis: ''
                          }
                        };
            
                      Plotly.newPlot('lineplot', data1, layout);
                      Plotly.newPlot('scatterplot', data2, layout2)
                      Plotly.newPlot("boxplot1", data3, layout3);
                      Plotly.newPlot("boxplot2", data4, layout4)
            
            
            
            
                getPearsonCorrelation(priceBrah,priceBrah2);
               
                });
            
            
            
            };
            
            d3.selectAll("#submit").on("click", respond_to_button);
            

Bonus Javascript File



                console.log("Script is here");

                // /** 
                // * @param {array} rows
                // * @param {integer} index
                
                // */
                
                function unpack(rows, index) {
                    return rows.map(function(row) {
                      return row[index];
                    });
                  };
                
                
                function respond_to_button() {
                    d3.event.preventDefault();
                    console.log("Your button is reacting");
                    var userSelectedCrypto1 = d3.select("#firstCurrency").node().value;
                    var userSelectedCrypto2 = d3.select("#secondCurrency").node().value;
                    var userSelectedDateTime1 = d3.select("#firstDateTime").node().value;
                    var userSelectedDateTime2 = d3.select("#secondDateTime").node().value;
                    console.log(userSelectedCrypto1);
                    console.log(userSelectedDateTime1);
                
                    buildPlot(userSelectedCrypto1, userSelectedCrypto2, userSelectedDateTime1, userSelectedDateTime2);
                };
                
                function buildPlot(userSelectedCrypto1, userSelectedCrypto2, userSelectedDateTime1, userSelectedDateTime2) {
                
                    var corr_data_fetch_url = `/livedata/${userSelectedCrypto1}/${userSelectedCrypto2}/${userSelectedDateTime1}/${userSelectedDateTime2}`;
                
                    d3.json(corr_data_fetch_url).then(function(data) {
                
                        // Grab values from the response json object to build the plots
                        var symbol = unpack(data.dataset.data, 0);
                        var price = unpack(data.dataset.data, 1);
                        var timestamp = unpack(data.dataset.data, 2);
                
                        console.log(symbol);
                        console.log(price);
                        console.log(timestamp);
                
                
                
                        var halfWayThrough = Math.floor(timestamp.length / 2)
                
                        var symbolBrah = symbol.slice(0, halfWayThrough);
                        var symbolBrah2 = symbol.slice(halfWayThrough, symbol.length);
                        var priceBrah = price.slice(0, halfWayThrough);
                        var priceBrah2 = price.slice(halfWayThrough, price.length);
                        var datetimeBrah = timestamp.slice(0, halfWayThrough);
                
                
                        minutesInt = Array.from(Array(datetimeBrah.length).keys())
                        console.log(minutesInt)
                
                        var trace1 = {
                            x: minutesInt,
                            y: priceBrah,
                            name: `${userSelectedCrypto1}`,
                            type: 'scatter'
                          };
                          var trace2 = {
                            x: minutesInt,
                            y: priceBrah2,
                            name: `${userSelectedCrypto2}`,
                            type: 'scatter'
                          };
                          
                          var data1 = [trace1, trace2];
                          
                          trace3 = {
                            x: priceBrah,
                            y: priceBrah2,
                            name: `${userSelectedCrypto1} & ${userSelectedCrypto2}`,
                            type: 'scatter',
                            mode: 'markers'
                          }
                          
                          var data2 = [trace3];
                          
                        var trace4 = {
                            y: priceBrah,
                            type: "box"
                        };
                        var trace5 = {
                            y: priceBrah2,
                            type: "box"
                        };
                
                        var data3 = [trace4]
                        var data4 = [trace5]
                          
                          
                          var layout = {
                            title: {
                              text:'Plot Title',
                              font: {
                                family: 'Courier New, monospace',
                                size: 24
                              },
                              xref: 'paper',
                              x: 0.05,
                            },
                            xaxis: {
                              title: {
                                text: 'x Axis',
                                font: {
                                  family: 'Courier New, monospace',
                                  size: 18,
                                  color: '#7f7f7f'
                                }
                              },
                            },
                            yaxis: {
                              title: {
                                text: 'y Axis',
                                font: {
                                  family: 'Courier New, monospace',
                                  size: 18,
                                  color: '#7f7f7f'
                                }
                              }
                            }
                          };
                          
                
                          var layout2 = {
                            title: {
                              text:'Plot Title',
                              font: {
                                family: 'Courier New, monospace',
                                size: 24
                              },
                              xref: 'paper',
                              x: 0.05,
                            },
                            xaxis: {
                              title: {
                                text: 'x Axis',
                                font: {
                                  family: 'Courier New, monospace',
                                  size: 18,
                                  color: '#7f7f7f'
                                }
                              },
                            },
                            yaxis: {
                              title: {
                                text: 'y Axis',
                                font: {
                                  family: 'Courier New, monospace',
                                  size: 18,
                                  color: '#7f7f7f'
                                }
                              }
                            }
                          };
                
                
                
                
                          Plotly.newPlot('lineplot', data1, layout);
                          Plotly.newPlot('scatterplot', data2, layout2)
                          Plotly.newPlot("boxplot1", data3);
                          Plotly.newPlot("boxplot2", data4)
                
                      // Plotly.newPlot("lineplot", trace1, layout);
                    });
                
                };
                
                d3.selectAll("#submit").on("click", respond_to_button);