----------------------------------------------------------
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">
   
Stock: <input type="text" name="stock" id="stock" value="YHOO"
style="width: 70px;">
   
Start: <input type="text" name="start" id="start" value="2013-08-10"
style="width: 80px;">
   
End: <input type="text" name="end" id="end" value="2014-03-10"
style="width: 80px;">
   
<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">
   
Stock: <input type="text" name="stock" id="stock" value="YHOO"
style="width: 70px;">
   
Start: <input type="text" name="start" id="start" value="2013-08-10"
style="width: 80px;">
   
End: <input type="text" name="end" id="end" value="2014-03-10"
style="width: 80px;">
   
<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 (
stock, start 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 High, Low 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 a
console.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 High, Low 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 :-)).
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?
ReplyDeleteThanks 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