“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
I’ve created some codeblocks here, but you can also find the code in the getStockInfo.txt file in the attached zip file.
A few things about the above code:
- 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.
- 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.
- 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.
Change_PercentChange from yahoo.finance.quotes where symbol = "AXP"
- 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
- The YQL query upon execution returns a JSON object.
- Stock Information is retrieved from the JSON object and the buildMarkup() function is called.
- 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.
- The resulting HTML containing stock info is then displayed on the page.
- 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.
The file StockInfoFiles.zip file contains the following files that you will need:
- chicago.master – This contains references to the custom CSS file and the jQuery library.
- getStockInfo.txt – This contains the HTML div that displays the stock info, and the code shown in the codeblock above.
- stockInfo.css – This contains the custom CSS that I used.
How to Implement
- Download and extract the three files from the attached zip file.
- 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.
- Upload chicago.master file to the ‘Master pages and page layouts’ gallery in your SharePoint environment.
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.
- 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
- Save (and check-in the page, if needed) and you should see the stock info similar to the one shown below:
That’s all there is to it my friends. Have fun adding the Stock Info Web Part to your SharePoint site.