Raspberry Pi Pico Tips and Tricks

Sunday 16 March 2014

Dynamically retrieve historical stock market records via YQL and graph them with d3.js

The following post is a portion of the D3 Tips and Tricks book which is free to download. To use this post in context, consider it with the others in the blog or just download the the book as a pdf / epub or mobi .
----------------------------------------------------------

Dynamically retrieve historical stock records via YQL

Purpose

This page was developed to be an attempt to integrate the ability to download time range data from the Yahoo! Developer Network via a YQL query and to be able to edit that query and dynamically adjust the output graph.
It doesn’t hurt that the data is pretty interesting (who isn’t fascinated by the rise and fall of stock prices?).
The following is a picture of the resulting graph;
Dynamic historical stock graph

The code

The following is the full code for the example. A live version is available online at bl.ocks.org or GitHub. It is also available as the file ‘yql-dynamic-stock-line.html’ as a separate download with D3 Tips and Tricks. A a copy of most the files that appear in the book can be downloaded (in a zip file) when you download the book from Leanpub.
<!DOCTYPE html>
<meta charset="utf-8">
<style> /* set the CSS */

body { font: 12px Arial;}

path { 
    stroke: steelblue;
    stroke-width: 2;
    fill: none;
}

text.shadow {
    stroke: white;
    stroke-width: 2.5px;
    opacity: 0.9;
}

.axis path,
.axis line {
    fill: none;
    stroke: grey;
    stroke-width: 1;
    shape-rendering: crispEdges;
}

</style>
<body>

<!-- set inputs for the query -->    
<div id="new_input">
    &nbsp &nbsp
    Stock: <input type="text" name="stock" id="stock" value="YHOO" 
    style="width: 70px;">
    &nbsp &nbsp
    Start: <input type="text" name="start" id="start" value="2013-08-10"
    style="width: 80px;">
    &nbsp &nbsp
    End: <input type="text" name="end" id="end" value="2014-03-10" 
    style="width: 80px;">
    &nbsp &nbsp
    <input name="updateButton" 
    type="button" 
    value="Update" 
    onclick="updateData()" />
</div>

<!-- load the d3.js library -->    
<script src="http://d3js.org/d3.v3.min.js"></script>

<script>

// Set the dimensions of the graph
var margin = {top: 30, right: 40, bottom: 30, left: 50},
    width = 600 - margin.left - margin.right,
    height = 270 - margin.top - margin.bottom;

// Parse the date / time
var parseDate = d3.time.format("%Y-%m-%d").parse;

// Set the ranges
var x = d3.time.scale().range([0, width]);
var y = d3.scale.linear().range([height, 0]);

var xAxis = d3.svg.axis().scale(x)
    .orient("bottom").ticks(5);

var    yAxis = d3.svg.axis().scale(y)
    .orient("left").ticks(5);

var valueline = d3.svg.line()
    .x(function(d) { return x(d.date); })
    .y(function(d) { return y(d.high); });
  
var svg = d3.select("body")
    .append("svg")
        .attr("width", width + margin.left + margin.right)
        .attr("height", height + margin.top + margin.bottom)
      .append("g")
        .attr("transform", "translate(" 
            + margin.left 
            + "," + margin.top + ")");

var stock = document.getElementById('stock').value;
var start = document.getElementById('start').value;
var end = document.getElementById('end').value;

var inputURL = "http://query.yahooapis.com/v1/public/yql"+
    "?q=select%20*%20from%20yahoo.finance.historicaldata%20"+
    "where%20symbol%20%3D%20%22"
    +stock+"%22%20and%20startDate%20%3D%20%22"
    +start+"%22%20and%20endDate%20%3D%20%22"
    +end+"%22&format=json&env=store%3A%2F%2F"
    +"datatables.org%2Falltableswithkeys";

    // Get the data 
    d3.json(inputURL, function(error, data){

    data.query.results.quote.forEach(function(d) {
        d.date = parseDate(d.Date);
        d.high = +d.High;
        d.low = +d.Low;
    });

    // Scale the range of the data
    x.domain(d3.extent(data.query.results.quote, function(d) {
        return d.date; }));
    y.domain([
        d3.min(data.query.results.quote, function(d) { return d.low; }), 
        d3.max(data.query.results.quote, function(d) { return d.high; })
    ]);

    svg.append("path")        // Add the valueline path.
        .attr("class", "line")
        .attr("d", valueline(data.query.results.quote));

    svg.append("g")            // Add the X Axis
        .attr("class", "x axis")
        .attr("transform", "translate(0," + height + ")")
        .call(xAxis);

    svg.append("g")            // Add the Y Axis
        .attr("class", "y axis")
        .call(yAxis);

    svg.append("text")          // Add the label
        .attr("class", "label")
        .attr("transform", "translate(" + (width+3) + "," 
            + y(data.query.results.quote[0].high) + ")")
        .attr("dy", ".35em")
        .attr("text-anchor", "start")
        .style("fill", "steelblue")
        .text("high");

    svg.append("text")          // Add the title shadow
        .attr("x", (width / 2))
        .attr("y", margin.top / 2)
        .attr("text-anchor", "middle")
        .attr("class", "shadow")
        .style("font-size", "16px")
        .text(stock);
        
    svg.append("text")          // Add the title
        .attr("class", "stock")
        .attr("x", (width / 2))
        .attr("y", margin.top / 2)
        .attr("text-anchor", "middle")
        .style("font-size", "16px")
        .text(stock);
});

// ** Update data section (Called from the onclick)
function updateData() {

var stock = document.getElementById('stock').value;
var start = document.getElementById('start').value;
var end = document.getElementById('end').value;

var inputURL = "http://query.yahooapis.com/v1/public/yql"+
    "?q=select%20*%20from%20yahoo.finance.historicaldata%20"+
    "where%20symbol%20%3D%20%22"
    +stock+"%22%20and%20startDate%20%3D%20%22"
    +start+"%22%20and%20endDate%20%3D%20%22"
    +end+"%22&format=json&env=store%3A%2F%2F"
    +"datatables.org%2Falltableswithkeys";

    // Get the data again
    d3.json(inputURL, function(error, data){

        data.query.results.quote.forEach(function(d) {
            d.date = parseDate(d.Date);
            d.high = +d.High;
            d.low = +d.Low;
        });

        // Scale the range of the data
        x.domain(d3.extent(data.query.results.quote, function(d) {
            return d.date; }));
        y.domain([
            d3.min(data.query.results.quote, function(d) { 
                return d.low; }), 
            d3.max(data.query.results.quote, function(d) { 
                return d.high; })
        ]);

        // Select the section we want to apply our changes to
        var svg = d3.select("body").transition();

        // Make the changes
        svg.select(".line")    // change the line
            .duration(750) 
            .attr("d", valueline(data.query.results.quote));

        svg.select(".label")   // change the label text
            .duration(750)
            .attr("transform", "translate(" + (width+3) + "," 
            + y(data.query.results.quote[0].high) + ")");
 
        svg.select(".shadow") // change the title shadow
            .duration(750)
            .text(stock);  
             
        svg.select(".stock")   // change the title
            .duration(750)
            .text(stock);
     
        svg.select(".x.axis") // change the x axis
            .duration(750)
            .call(xAxis);
        svg.select(".y.axis") // change the y axis
            .duration(750)
            .call(yAxis);
    });
}

</script>
</body>

The description

Firstly, I have not included any form of validation or sanitising of the input fields. If you were to build something that was being used in a serious way, that would be essential.
Secondly, there are limits on what the YQL query will return. I have found that there appears to be a limit on the date range allowed (although I’m not sure what that limit is) and there is of course a limit to what the Yahoo! Developer Network will support for different end use cases. If you want to use the data for comericial reasons or if your use is heavy, you will need to contact them to arrange for some form of agreement to use the data appropriately.
To use the graph all you need to do is enter a valid ticker symbol and a start / end date range where the date is formatted as yyyy/mm/dd. As I noted earlier, there appears to be a range limit, so feel free to experiment a bit to work it out if necessary to your use.
The ticker symbol and start/stop dates
The section to get the input fields was something new to me as normally I would use bootstrap.js with it’s wealth of form input options. But the following section in the HTLM portion was neat enough to get the required input.
<div id="new_input">
    &nbsp &nbsp
    Stock: <input type="text" name="stock" id="stock" value="YHOO" 
    style="width: 70px;">
    &nbsp &nbsp
    Start: <input type="text" name="start" id="start" value="2013-08-10"
    style="width: 80px;">
    &nbsp &nbsp
    End: <input type="text" name="end" id="end" value="2014-03-10" 
    style="width: 80px;">
    &nbsp &nbsp
    <input name="updateButton" 
    type="button" 
    value="Update" 
    onclick="updateData()" />
</div>
Of course it needs to be coupled with a JavaScript section to allow it to use the inputted fields in the query but that was also nice and easy with the following section of code;
var stock = document.getElementById('stock').value;
var start = document.getElementById('start').value;
var end = document.getElementById('end').value;
The HTML portion includes the onclick="updateData()" code that allows the JavaScript updateData function to be called that reloads new data from the Yahoo! Developer Network and updates the d3.js objects.
This particular file uses the ‘load everything first’ then ‘update everything that needs updating’ model that was followed in the earlier chapter on creating a graph that loads data dynamically.
The YQL query is declared as a variable in the following section;
var inputURL = "http://query.yahooapis.com/v1/public/yql"+
    "?q=select%20*%20from%20yahoo.finance.historicaldata%20"+
    "where%20symbol%20%3D%20%22"
    +stock+"%22%20and%20startDate%20%3D%20%22"
    +start+"%22%20and%20endDate%20%3D%20%22"
    +end+"%22&format=json&env=store%3A%2F%2F"
    +"datatables.org%2Falltableswithkeys";
It has had line feeds deliberately introduced to make formatting on the pages of the book easier (otherwise the publishing process introduces additional characters). In it you can see the addition of the variables that allow the query to be executed (stockstart and end).
Immediately after loading the data we run it through a forEach loop that goes to the location in the JSON hierarchy where the HighLow and Date values are stored and it ensures that the high and low values are correctly recognises as numbers and formats the date.
    data.query.results.quote.forEach(function(d) {
        d.date = parseDate(d.Date);
        d.high = +d.High;
        d.low = +d.Low;
    });
This is quite interesting because it provides a peek at the structure of the JSON. This is a pretty important piece of information because without the structure, it is not possible to correctly address the data you want. I’m not sure what the best method would be for determining the structure of the returned data, but I simply use aconsole.log(data) call after the data is loaded while I am developing the file and this allows me to explore and note the structure.
The following screen-shot illustrates the method;
console.log(data) structure
You should be able to discern the .query.results.quote pathway that leads to the HighLow and Date values.
The remainder of the code is a repetition of examples explained in the remainder of the book. Most especially in the simple line graph area.
The description above (and heaps of other stuff) is in the D3 Tips and Tricks book that can be downloaded for free (or donate if you really want to :-)).

2 comments:

  1. First, thanks for your work, it's amazing! Now, I am trying to make this work, but even your bl.ocks.org example does not seem to work. Is it because Yahoo changed something?

    ReplyDelete
    Replies
    1. Thanks for the compliment. You're fight about the example. I haven't dug into why it's failing, but will get to it after the current chapter I'm working on. Thanks for the heads up.

      Delete