Tuesday, February 25, 2014

How to Display Stock Information in SharePoint 2013

“If you don’t follow the stock market, you are missing some amazing drama.”

Mark Cuban, Owner of the Dallas Mavericks

We have all at some point been asked by our clients for a Stock Information Web Part for their SharePoint site. If you have not been asked for this yet, don’t worry. Hopefully this blog will come in handy. And you can be the magnificent Hero that added amazing drama to your client’s SharePoint site. J

This recipe calls for some JavaScript, HTML, and a smidgen of YQL (Yahoo Query Language). Mix all these ingredients and serve it in style in the new SharePoint 2013 ‘Script Editor’ web part.

I’ve created some codeblocks here, but you can also find the code in the getStockInfo.txt file in the attached zip file.

Lines 1 through 32 show the <div> tag in which the stock info will be rendered, the YQL to retrieve stock info from the Yahoo! Finance site, JavaScript code to retrieve stock info from the JSON object, and the call to the function that builds the markup.

Lines 34 to 66 contain the JavaScript function in which the markup is built with the appropriate classes.

<div class="stockInfo"></div>

 <script>

            var url = "https://query.yahooapis.com/v1/public/yql?q=select%20Symbol%2CName%2CDaysLow%2CDaysHigh%2CYearLow%2CYearHigh%2CLastTradePriceOnly%2CLastTradeDate%2CLastTradeTime%2CMarketCapitalization%2CChange_PercentChange%20from%20yahoo.finance.quotes%20where%20symbol%20%3D%20%22AXP%22%09&format=json&diagnostics=true&env=http%3A%2F%2Fdatatables.org%2Falltables.env";

            var arrQuote, stockPrice, daysHigh, daysLow, yearHigh, yearLow, symbol, company, lastTradeDate, lastTradeTime, marketCap, arrStockChangePercent, stockChange, stockChangePercent, stockChangeDirection, stockInfoMarkup;

            $.getJSON(url, function (data) {

                arrQuote = data.query.results.quote;

                stockPrice = parseFloat(Math.round(arrQuote["LastTradePriceOnly"] * 100) / 100).toFixed(2);

                daysHigh = arrQuote["DaysHigh"];

                daysLow = arrQuote["DaysLow"];

                yearHigh = arrQuote["YearHigh"];

                yearLow = arrQuote["YearLow"];

                symbol = arrQuote["Symbol"];

                company = arrQuote["Name"];

                lastTradeDate = arrQuote["LastTradeDate"];

                lastTradeTime = arrQuote["LastTradeTime"];

                marketCap = arrQuote["MarketCapitalization"];

                arrStockChangePercent = arrQuote["Change_PercentChange"].split(" - ");

                stockChange = arrStockChangePercent[0];

                stockChangePercent = arrStockChangePercent[1];

                stockChangeDirection = stockChange.charAt(0);

                if (stockChangeDirection === "+") {

                    stockChangeDirection = "up";

                }

                else {

                    stockChangeDirection = "down";

                }

                stockInfoMarkup = buildMarkup(stockPrice, daysHigh, daysLow,

                                              yearHigh, yearLow, symbol,

                                              company, lastTradeDate, lastTradeTime,

                                              marketCap, stockChange, stockChangePercent, stockChangeDirection);

                $('.stockInfo').append(stockInfoMarkup);

            });



            function buildMarkup(stockPriceVal, daysHighVal, daysLowVal,

                                 yearHighVal, yearLowVal, symbolVal,

                                 companyVal, lastTradeDateVal, lastTradeTimeVal,

                                 marketCapVal, changeVal, changePercentVal, changeDirectionVal) {

                stockInfoMarkup = "<div class=\"companyTitle\"><a href='http://finance.yahoo.com/q?s=" + symbolVal + "' target=\"_blank\">" + companyVal + " (Symbol: " + symbolVal + ")</a></div>";

                stockInfoMarkup += "<div class=\"companySymbolDateTime\">" + lastTradeDateVal + " - " + lastTradeTimeVal + " ET</div>";

                stockInfoMarkup += "<span class=\"companyStockPrice\">" + stockPriceVal + "</span>";

                if (changeDirectionVal === "up") {

                    stockInfoMarkup += "<span class=\"companyStockUp\">" + changeVal + " (" + changePercentVal + ")</span>";

                }

                else {

                    stockInfoMarkup += "<span class=\"companyStockDown\">" + changeVal + " (" + changePercentVal + ")</span>";

                }

                stockInfoMarkup += "<div></div>";

                stockInfoMarkup += "<div>";

                stockInfoMarkup += "<div class=\"companyStockLabels\">";

                stockInfoMarkup += "<div>Days High</div>";

                stockInfoMarkup += "<div>Days Low</div>";

                stockInfoMarkup += "<div>52 Week High</div>";

                stockInfoMarkup += "<div>52 Week Low</div>";

                stockInfoMarkup += "<div>Market Capitalization</div>";

                stockInfoMarkup += "</div>";

                stockInfoMarkup += "<div class=\"companyStockValues\">";

                stockInfoMarkup += "<div>" + daysHighVal + "</div>";

                stockInfoMarkup += "<div>" + daysLowVal + "</div>";

                stockInfoMarkup += "<div>" + yearHighVal + "</div>";

                stockInfoMarkup += "<div>" + yearLowVal + "</div>";

                stockInfoMarkup += "<div>" + marketCapVal + "</div>";

                stockInfoMarkup += "</div>";

                stockInfoMarkup += "</div>";

                return stockInfoMarkup;

            }

        </script>

A few things about the above code:

  1. It relies on the jQuery library. So make sure that you reference the jQuery library in your code. I usually add a reference to it in the master page. You will see this jQuery reference in the chicago.master page in the attached zip file. Update the reference if you are using a different version of the jQuery library.
    <script type="text/javascript" src="http://code.jquery.com/jquery-1.10.2.min.js">
  2. As I mentioned earlier, it uses YQL (Yahoo Query Language) to retrieve stock info from Yahoo! Finance site. It queries the http://query.yahooapis.com/v1/public/yql?[query_params] site, and the service is free. Usage limits and restrictions info can be found on the YQL FAQ site.
  3. YQL is a SQL-like language (shown below is the SQL SELECT query that I am using in the code). In the code in the 1st screenshot above, spaces and commas in the SELECT query are encoded. You can use your favorite online Encode/Decode utility for this. I used the tool at Meyerweb.com to decode it. For readability purposes, shown below is the decoded version of the SELECT query. AXP is the stock symbol for American Express.
    select Symbol,Name,DaysLow,DaysHigh,YearLow,YearHigh,
    LastTradePriceOnly,LastTradeDate,LastTradeTime,MarketCapitalization,
    Change_PercentChange from yahoo.finance.quotes where symbol = "AXP"
  4. You can add or remove columns, and change the value in the WHERE clause in the above SELECT query. To view all available columns, run a SELECT * query on the Yahoo YQL Console page. Click the ‘Test’ button, after the page is loaded. This will show all the columns that you can query and the values in it. The screenshot below shows the Yahoo YQL console with a SELECT query for retrieving stock info for American Express (Symbol: AXP). Change the symbol value in the WHERE clause to match the symbol (example: MSFT for Microsoft) of the company whose stock info needs to be retrieved. The resulting JSON object is displayed in the console. You can copy the REST Query code (shown in screenshot below) that is auto-generated in the console, and then paste it in your code. Note that the query is encoded in the REST Query. This is a good thing, because the encoded version is needed in the code. Once you have the proper REST Query generated, you need to append it to this URL: https://query.yahooapis.com/v1/public/yql?q=

    For example, if you have generated the REST Query for retrieving Microsoft (Symbol:MSFT) stock info, you will need to append the SELECT query after the = sign as shown in the highlighted portion below.
    var url = "https://query.yahooapis.com/v1/public/yql?q=select%20Symbol%2CName%2CDaysLow%2CDaysHigh%2CYearLow%2CYearHigh
    %2CLastTradePriceOnly%2CLastTradeDate%2CLastTradeTime%2CMarketCapitalization%2CChange_PercentChange
    %20from%20yahoo.finance.quotes%20where%20symbol%20%3D%20%22MSFT%22%09&format=json&diagnostics=true
    &env=http%3A%2F%2Fdatatables.org%2Falltables.env";

    clip_image006
  5. The YQL query upon execution returns a JSON object.
  6. Stock Information is retrieved from the JSON object and the buildMarkup() function is called.
  7. HTML with the appropriate CSS classes is built in the buildMarkup() function. Classes that I used are in the stockInfo.css file in the attached zip file.
  8. The resulting HTML containing stock info is then displayed on the page.
  9. Percent change value is displayed using a different CSS class, based on a positive or a negative value. The screenshot below shows stock information of Companies with positive and negative percent changes respectively. The Company Name is a clickable link that goes to the company stock details page on finance.yahoo.com. Date is displayed in Month/Day/Year format. The time value is based on Eastern Time (ET) zone.

clip_image008

clip_image010

The file StockInfoFiles.zip file contains the following files that you will need:

  1. chicago.master – This contains references to the custom CSS file and the jQuery library.
  2. getStockInfo.txt – This contains the HTML div that displays the stock info, and the code shown in the codeblock above.
  3. stockInfo.css – This contains the custom CSS that I used.

How to Implement

  1. Download and extract the three files from the attached zip file.
  2. Upload the stockInfo.css file to an appropriate location in your SharePoint environment. I uploaded it to /_catalogs/masterpage/chicago/styles folder in my environment.
  3. Upload chicago.master file to the ‘Master pages and page layouts’ gallery in your SharePoint environment.
    OR
    Just add the CSS and jQuery references in the existing masterpage in your SharePoint environment. If you do this, remember to update the paths to the CSS and jQuery libraries in the masterpage, to match the paths in your environment. You don’t need to add the jQuery library reference, if your existing masterpage already has it. Also, you can simply add the styles in the stockInfo.css to the existing css file in your environment.
  4. Copy the entire code in the getStockInfo.txt file, and paste it in a ‘Script Editor’ web part on a page in your SharePoint site.
    NOTE: Remember to change the symbol value in the var url line after pasting in the code. See highlighted portion below. AXP is the symbol for American Express. Change it to the symbol of the company that you are interested in.
    var url = "https://query.yahooapis.com/v1/public/yql?q=select%20Symbol%2CName%2CDaysLow%2CDaysHigh%2CYearLow
    %2CYearHigh%2CLastTradePriceOnly%2CLastTradeDate%2CLastTradeTime%2CMarketCapitalization
    %2CChange_PercentChange %20from%20yahoo.finance.quotes%20where%20symbol
    %20%3D%20%22AXP%22%09&format=json&diagnostics=true& env=http%3A%2F%2Fdatatables.org
    %2Falltables.env">https://query.yahooapis.com/v1/public/yql?q=select%20Symbol%2CName
    %2CDaysLow%2CDaysHigh%2CYearLow%2CYearHigh%2CLastTradePriceOnly
    %2CLastTradeDate%2CLastTradeTime %2CMarketCapitalization%2CChange
    _PercentChange%20from%20yahoo.finance.quotes
    %20where%20symbol%20%3D%20%22AXP%22%09&format=json&diagnostics=true
    &env=http%3A%2F%2Fdatatables.org%2Falltables.env";

    clip_image014

  5. Save (and check-in the page, if needed) and you should see the stock info similar to the one shown below:
    clip_image015

That’s all there is to it my friends. Have fun adding the Stock Info Web Part to your SharePoint site.