Shares Data 2. Graph in Excel from Web and Graph from API to web page

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

In the previous post “Stock Data 1. Capture via API and Excel from Web” I used Excel to grab some data from the Web & show it as a Table inside Excel. After bringing the data in I had trouble creating a chart from the Table.

So, after messing around with the Charts again today I finally figured out what was happening. Wrong data type!!!!

Process for making Chart from imported Table from, Web in Excel

After importing the table into Excel, go to the QUERY tab and select Edit (you may need to be in a cell in the Table or select the table prior to the Edit command)

This opens the Table in Power Query Editor.

The Column headers show data type.  This is what confused me as the first column data type in DATE ( red Arrow) but all the rest are TEXT (blue Arrows). ( Note, in the normal excel view, clicking on the columns and choosing FORMAT/NUMBER doesn’t work). As

So for a Numeric Graph the data is not going to display if its in a TEXT format. But the Date (on the X axis displays fine).

To change the data type double-click on column header  at the “ABC” text and a pop-up box comes up and you can choose your Data Type.

After you have selected a new Data Type Power Query asks if you want to Change Column type – Select “ADD NEW STEP” and in the Query Settings TAB on the Right of the Screen a new Applied Step is added.

You will also see the data in the column shift from the LEFT to the RIGHT.

This is something obvious for Excel Users who use it all the time, that TEXT is Left formatted in the Cell and Numbers are RIGHT formatted in the Cell. ( As I play with a lot of programmes I had forgotten about that)- but obvious once it is pointed out).

So change all the Columns to your preferred data type

When you go to Close Power Query Editor it asks you if you want to keep the Changes. Yes you do. This becomes another part of the process when you update the data (Refresh from Web  with the DATA tab, Refresh All icon) so it will reimport the updated data and reformat the columns to your preferred data type).

After closing Power Query Editor you should see the numbers shift to the RIGHT of the cells. Then select the table, and in the Insert Tab choose your Graph type ( follow videos on youtube making Charts, there are a lot out there)

And you get your CHART.

To see that it all works do a Refresh on the Data and the Chart should still be working. This means the changing Column Data Type has become part of the update process.

        

API data for graphics

Alpha Vantage API

I have been spending a bit of time trying to get the JavaScript working. Thinking that I’d use the code that I used on the Weather App Get request (See API 4. Some tests with different APIโ€™s ). But I couldn’t get the JavaScript to display the JSON data. It kept on finding fault with the code. I could get the JSON data but I could not find a JavaScript method to extract the data to display on a web page. I kept on getting errors in the code.

I finally found this article with code that used JS, CSS & HTML to display latest information from the JSON file, but it only displays the latest date data.  I could not get the :

document.write(data[“Weekly Time Series”][0][“1. open”]);  where you call an index to the data, in this case index [0] to work. And the dates were weekday dates , so there was a jump over the weekend. So you’d need to call the index by date, and you would have to change the date with later calls to the api when all the dates changed.

So 1/ not easy to call the data accurately, 2/ Not easy to iterate over the data.

{
    "Meta Data": {
        "1. Information": "Weekly Prices (open, high, low, close) and Volumes",
        "2. Symbol": "AMP.NZ",
        "3. Last Refreshed": "2018-07-31",
        "4. Time Zone": "US/Eastern"
    },
    "Weekly Time Series": {
        "2018-07-31": {
            "1. open": "3.6000",
            "2. high": "3.7500",
            "3. low": "3.5900",
            "4. close": "3.7300",
            "5. volume": "74373"
        },
        "2018-07-26": {
            "1. open": "3.9300",
            "2. high": "3.9300",
            "3. low": "3.6400",
            "4. close": "3.6400",
            "5. volume": "174516"
        },
        "2018-07-19": {
            "1. open": "3.9500",
            "2. high": "4.0100",
            "3. low": "3.9000",
            "4. close": "3.9700",
            "5. volume": "66229"
        },

So a bit frustrating, I spent a lot of time trying to get this to work.

One comment before I leave Alpha Vantage, there is PredIQive  that you can put in the information and it gives you the data in a file format. That is great, but if I had the file I’d then have to process it somehow to display on a web page. I could just have easily used Excel Web page capture process above. In this area I am trying to use API calls.

There was a comment in the JavaScript section on Charts used in this website. So I started looking at the page source for how the process had been set up. In the end I found that the source of the data was from:

https://api.iextrading.com

This has free data calls, but unfortunately only US data. So I decided to use that as a data source instead as the data is nicely indexed (see below). For the point of this exercise this is OK.

So I now have a data source that I can extract data from the JSON file.

I have spent most of the day trying to get the Javascript Array Data for Date & Closing prices from my Get function to put it into Charts.js line Graph with no success.

Although I have specified Global Array variables  and then used  an iterate process through the JSON file to .push to the Array variables, the data in the Array Variables seems only to be accessed within the function, even trying dataX1 =dataX.slice() to copy the data from a local array to a global array doesn’t work.

let dataX = [];
let dataY = [];

$(document).ready(function myJ(){
$.getJSON(“https://api.iextrading.com/1.0/stock/AAPL/batch?types=quote,chart&range=1m&last=5”, function(data){
for (var i = 0; i < data[“chart”].length; i++ ){
dataX.push(data[“chart”][i][“date”]);
dataY.push(data[“chart”][i][“close”]);
document.write(dataX[i]);
document.write(“<br/>”);
document.write(dataY[i]);
document.write(“<br/>”);

In the end I did a document.write(dataX[i])  document.write(dataY[i]) for the 2 arrays and copied them to a CSV and opened that in Notepad ++ to make an array so that I had simple arrays in the code to demonstrate the line Graph.

Line Graph displayed on Web Page.

This is created with ChartsJS.

Note, if you click the pink rectangle at Title at the top the line disappears. This is great when you have several lines of data that may overlay each other in parts.



Image of graph above as in LinkedIn sometimes code does not seem to run on phones. Ok on laptop.

End Comment

I actually thought both processes would be easier than they were. Little traps that caught me out, but once rectified the process went well for Excel. I still have to solve the JavaScript challenge.

The Excel process took a time as I had the wrong data type being imported, so could not graph it.Once that was done and the extra step of data type conversion added graphing was simple.
Within Excel there are some good graphing tweeks so you get pretty graphs and you can setup some nice dashboards.

The API process is there. I can get the data via the API, so can call the latest data to the web page.The charts are quite good too. See Stock Picker Website for some ChartJS chartss. I think they are pretty good.I just haven’t been able to get the array across to the Charting code yet.

So there will need to be another post about the visualisation of the information. Only after I’ve sorted out the array transfer issue. Then I think some exploration of the presentation of the data. I’m interested in doing a bit more exploring with ChartJS and also I’d like to play some more with the D3.js library too. I think there are a few more libraries. In fact, there are a few posts about them here and here.

2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *