Data Server 3 (stockprice file download)

For the last part of django data server setup, I will implement a file download feature. The completed page can be previewed here.

Since I’ve created a browser side (data/templates/stockprice.html)and server side code to (data/views/stockprice.py) in the previous post, I will append additional codes to the existing files. As a reminder, this is the file structure of django code at the end of previous posting.

│  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

As previously, the first step is to create HTML elements where handsontable objects will be created.

The HTML code is as follows.

<div class = "row">
	<div class="col-sm-6">
		<div id="parameters"></div>
		<div style = "margin-top:10px" id="download_dates"></div>
		<button type="button", class = "btn", onclick = "download_prices()">Download</button>
	</div>
	<div class = "col-sm-6"><div id="parameters2"></div></div>
	
</div>

In order to create the handsontables in parameters, parameters2, and download_datse the javascript code is as follows.

var container = document.getElementById('parameters');
var parameters_hot = new Handsontable(container, {licenseKey:'non-commercial-and-evaluation',
  data:[['KOSPI+KOSDAQ','monthly','csv']],
  minCols: 3, maxCols: 3,
  minRows: 1, maxRows: 1,
  colHeaders: ['Scope','Frequency','Format'],
  colWidths:[200,100,100],
  className: "htCenter",
  columns: [{type:'dropdown', source:['KOSPI+KOSDAQ','KOSPI','KOSDAQ']},{type:'dropdown', source:['monthly','yearly']},{type:'dropdown', source:['csv','excel']}],
});

var container = document.getElementById('parameters2');
var parameters2_hot = new Handsontable(container, {licenseKey:'non-commercial-and-evaluation',
  data:[[false,'Opening Price'],[false,'Low Price'],[false,'High Price'],[false,'Closing Price'],[true,'Adjusted Closing Price'],[true,'Shares Outstanding']],
  minCols: 2, maxCols: 2,
  minRows: 6, maxRows: 6,
  colHeaders: ['','Data'],
  colWidths:[50,200],
  className: ["htCenter"],
  columns: [{type:'checkbox'},{readOnly:true}],
});

var container = document.getElementById('download_dates');
var download_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}]
});

The datevalidator function is identical to one in the previous post. In parameters handsontable, the user can choose the universe of stocks (KOSPI and/or KOSDAQ), frequency of data points (daily is not included due to size of the resulting file), and resulting file structure. In parameters2 handsontable, the user can choose data columns.

The last addition to the data/templates/stockprice.html file is request to the server. Due to modern browsers’ restrictions on file download protocol directly from ajax request, a hyperlink is dynamically created after successful ajax request and clicked.

function download_prices(){
$.ajax({
    url: "{% url 'data:spvcreate_data' %}",
    data: {parameters:JSON.stringify(parameters_hot.getData()),
    parameters2:JSON.stringify(parameters2_hot.getData()),
    download_dates:JSON.stringify(download_dates_hot.getData()),
    },
    dataType: 'json',
    success: function (data) {
		a = document.createElement('a');
		a.href = "{% url 'data:spvdownload_data' %}?path="+data.filename;
		a.click();
    }
  })

}

Before moving to the server side scripts, I make a minor change to the database. I add month_end and year_end columns to store indicator for month end and year end for faster data retrieval. The database structure for stockprices table is as follows.

FieldTypeNullKey
isu_cdchar(6)NOPRI
tradedatedateNOPRI
open_prcint(11)NO
high_prcint(11)NO
low_prcint(11)NO
cls_prcint(11)NO
adj_prcfloat(13,2)YES
list_stock_volbigint(20)NO
exchangevarchar(10)NOMUL
month_endtinyint(1)YESMUL
year_endtinyint(1)YESMUL

Below is the server side code for creating data file and to start download protocol is as follows.

import uuid
from django.http import HttpResponse, Http404

def create_data(request):
	scope, frequency, format = json.loads(request.GET.get('parameters',None))[0]
	start_dt, end_dt = json.loads(request.GET.get('download_dates',None))[0]
	column_map = {'Opening Price':'open_prc','Low Price':'low_prc','High Price':'high_prc','Closing Price':'cls_prc','Adjusted Closing Price':'adj_prc','Shares Outstanding':'list_stock_vol'}
	columns = ['isu_cd','tradedate']
	for select, item in json.loads(request.GET.get('parameters2',None)):
		if select:
			columns.append(column_map[item])
	conditions = []
	if start_dt:
		conditions.append(f"tradedate >= '{start_dt}'")
	if end_dt:
		conditions.append(f"tradedate <= '{end_dt}'")
	if scope == 'KOSPI':
		conditions.append(f"exchange = 'STK'")
	if scope == 'KOSDAQ':
		conditions.append(f"exchange = 'KSQ'")
	if frequency == "monthly":
		conditions.append(f"month_end = 1")
	if frequency == "yearly":
		conditions.append(f"year_end = 1")
	con, cursor = get_con()
	cursor.execute(f"select {', '.join(columns)} from stockprices where {' and '.join(conditions)}")
	df = pd.DataFrame(list(cursor.fetchall()), columns = columns)
	if format == "csv":
		filename = str(uuid.uuid1())+".csv"
		df.to_csv(f"files/{filename}", index = False)
	if format == "excel":
		filename = str(uuid.uuid1())+".xlsx"
		df.to_excel(f"files/{filename}", index = False)
	con.close()
	return JsonResponse({'filename':filename})
	
def download_data(request):
	file_path = "files/"+request.GET.get('path',None)
	ext = file_path.split(".")[1]
	print(file_path)
	if os.path.exists(file_path):
		with open(file_path, 'rb') as fh:
			response = HttpResponse(fh.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
			response['Content-Disposition'] = f'attachment; filename="Stock_Prices.{ext}"'
			return response
	raise Http404
Tags: python  mysql  django  html  javascript 

Discussion and feedback