Data Server 2 (stockprice history with handsontable)

In the previous post, I’ve discussed setting up a django based webserver. In this post, I will build a simple webpage, which displays historical stockprices for selected stocks. The completed page can be previewed here.

After setting up django server, my project folder structure is as follows. In this post, I will work with few different files: data/templates/stockprice.html, data/views/stockprice.py, data/urls.py. In addition, I will add data/static/dbcon.py, which will store connection information for MySQL database.

│  manage.py
├─data
│  │  admin.py
│  │  apps.py
│  │  models.py
│  │  tests.py
│  │  urls.py
│  │  __init__.py
│  ├─migrations
│  ├─static
│  │  └─packages
│  │          jquery.min.js
│  ├─templates
│  │      base.html
│  │      stockprice.html
│  └─views
│     └─ stockprice.py
└─data_server
    │  settings.py
    │  urls.py
    │  wsgi.py
    └─ __init__.py

Let’s start by first looking at data/templates/stockprice.html file. I will make use of a javascript package called Handsontable, which produces grid like component in a page. Handsontable is free for non-commercial use and can be downloaded from its official website. To include it in the website, I will extract handsontable.full.css and handsontable.full.js files and include them in data/static/packages/handsontable folder.

The HTML code is as follows.

{% extends "base.html" %}
{% load static from staticfiles %}

{% block head %}
<link data-jsfiddle="common" rel="stylesheet" media="screen" href="{% static 'packages/handsontable/handsontable.full.css' %}">
<script data-jsfiddle="common" src="{% static 'packages/handsontable/handsontable.full.js' %}"></script>
{% endblock %}

{% block main %}
<h3>Korean Stock Prices</h3>
<div id="companyname"></div>
<button type="button", class = "btn", onclick = "add_stock()">Add Company</button>
<div style = "margin-top:10px" id="dates"></div>
<button type="button", class = "btn", onclick = "get_prices()">Show Prices</button>

<div style = "margin-top:10px" id="prices"></div>
{% endblock %}

In the head block, I’ve included the two handsontable files. In the main block, I’ve created three <div> elements to serve as container for three handsontables. I’ve also included two <button> elements to start functions add_stock and get_prices.

In the first <div> with id companyname, I will include a handsontable that can look for stockids based on company names. The javascript to create the handsontable is as follows.

var container = document.getElementById('companyname');
var companyname_hot = new Handsontable(container, {licenseKey:'non-commercial-and-evaluation',
  minCols: 1, maxCols: 1,
  minRows: 1, maxRows: 1,
  colHeaders: ['Company Name'],
  colWidths:[400],
  className: "htLeft",
  columns: [{type:'autocomplete'}],
});

The handsontable will be called companyname_hot and it will feature 1 cell with autocomplete feature. The source of autocomplete will be requested from the server through ajax.

$.ajax({
url: "{% url 'data:spvget_companylist' %}",
dataType: 'json',
success: function (data) {
  companyname_hot.setCellMeta(0,0,'source',data.companylist);
}})

The ajax request makes a request to a link {% url 'data:spvget_companylist' %} and sets the resulting companylist to companyname_hot. The next step is to edit data/views/stockprice.py and data/urls.py to allow the server to respond to this request. Firstly, I will add a function to data/views/stockprice.py file.

from django.http import JsonResponse
from data.static.dbcon import get_con

def get_companylist(request):
	con, cursor = get_con()
	cursor.execute("select * from companylist")
	result = cursor.fetchall()
	companylist = [f"{r[0]}: {r[1]}" for r in result]
	con.close()
	return JsonResponse({'companylist':companylist})

As stated earlier, data/static/dbcon.py is a file that contain connection information to MySQL database. Keeping connection information in one place makes code management easier in the future. data/static/dbcon.py code is as below. I’ve removed login credentials, and it is not included in the github repository.

import MySQLdb as sql

def get_con():
	con = sql.connect(host="eric-cho.com", port=3308, database="echo_blog", user="user_id", password="user_pw", charset="utf8")
	cursor = con.cursor()
	return con, cursor

The get_companylist function in data/views/stockprice.py submits query to companylist, which has the following structure. companylist table contains English names of KOSPI / KOSDAQ stocks as of February 19th, 2020. Since I’ve covered webscraping in in previous post, I won’t go into details, but the company names are scraped from English Dart and the code can be found here.

FieldTypeNullKey
isu_cdchar(6)NOPRI
companynamevarchar(100)YES

After adding an url in data/urls.py through path('spvget_companylist/', spv.get_companylist, name='spvget_companylist'),, I can check the response of link in a browser.

Moving on to the next handsontable, I will create a handsontable for start date and end date of stockprices.

var container = document.getElementById('dates');
var dates_hot = new Handsontable(container, {licenseKey:'non-commercial-and-evaluation',
  data:[['2019-01-01','2019-12-31']],
  minCols: 2, maxCols: 2,
  minRows: 1, maxRows: 1,
  colHeaders: ['Start Date', 'End Date'],
  colWidths:200,
  className: "htCenter",
  columns: [{validator:datevalidator, allowInvalid:false},{validator:datevalidator, allowInvalid:false}]
});

function datevalidator(s, callback){
  isdate = moment(s, 'YYYY-MM-DD',true).isValid();
  if (isdate){callback(true);}
  else{callback(false);}
}

The second handsontable, named dates_hot will have default values 2019-01-01 and 2019-12-31. The customer function datevalidator checks whether an input value is a valid date of form YYYY-MM-DD.

The last handsontable, named price_hot will display chosen stockids and their closing prices.

var max_stocks = 3;

var container = document.getElementById('prices');
var prices_hot = new Handsontable(container, {licenseKey:'non-commercial-and-evaluation',
  data:[['Stock ID']],
  minCols: 1, 
  maxCols: max_stocks+1, //max_stocks + tradedate column
  minRows: 1,
  colWidths:100,
  className: "htLeft",
  cells: function (row, col) {return {'readOnly':true};}
});

For the demo, I’ve chosen maximum number of stocks to 3, but it can be increased. I’ve locked the cells for editing, since the handsontable will only serve display purpose.

Now that all the handsontables are setup, the next step is to write the functions add_stock and get_prices. add_stock will add stockid of desired stock to the top row price_hot. get_prices will fetch the prices of these stocks from the server.

function add_stock(){
  var company = companyname_hot.getDataAtCell(0,0);
  var res = company.split(": ")
  if (company == null){
    alert('No stock selected');
    return;
  }
  n_cols = prices_hot.countCols();
  console.log(n_cols);
  if (n_cols==max_stocks + 1){
    alert('Maximum'+max_stocks+'stocks allowed');
    return;
  }else{
    prices_hot.setDataAtCell(0, n_cols, res[0]);
  }
}

function get_prices(){
$.ajax({
  url: "{% url 'data:spvget_prices' %}",
  data: {'dates': JSON.stringify(dates_hot.getDataAtRow(0)),'stock_list': JSON.stringify(prices_hot.getDataAtRow(0))},
  dataType: 'json',
  success: function (data) {
    prices_hot.loadData(data.prices);
  }})
}

The last part is to write a function in data/views/stocks.py, which will handle request made to {% url 'data:spvget_prices' %}.

import json
import pandas as pd

def get_prices(request):
	stock_list = json.loads(request.GET.get('stock_list',None))
	start_dt, end_dt = json.loads(request.GET.get('dates',None))
	con, cursor = get_con()
	for i,stockid in enumerate(stock_list[1:]):
		cursor.execute("select tradedate, cls_prc from stockprices where isu_cd = %s and tradedate>=%s and tradedate<=%s", [stockid, start_dt, end_dt])
		if i == 0:
			df = pd.DataFrame(list(cursor.fetchall()), columns = ['tradedate', stockid])
		else:
			df = pd.merge(df, pd.DataFrame(list(cursor.fetchall()), columns = ['tradedate', stockid]), how = "outer", on = "tradedate")
	prices = [stock_list]+df.values.tolist()
	return JsonResponse({'prices':prices})

After adding the url to data/urls.py using path('spvget_prices/', spv.get_prices, name='spvget_prices'),, the exercise is complete.

Tags: python  mysql  django  html  javascript 

Discussion and feedback