Wednesday, September 12, 2012

SharePoint 2010 Stock Quote

So you want to add a stock quote to your SharePoint site? Something that will work on SharePoint 2010 or SharePoint Online. Want something painless to implement? I searched high and low and I finally found this gem. I can't remember where I got the idea of using rest calls, but when I remember I'll put your credit on here. In any event, it bears repeating the steps here and I'm also adding a little XSLT formatting for my contribution.

The hardest part of this whole process is the XSL, but I'm giving it to you below so all you have to do is cut and paste.

Here is what it will look like:

SymbolLastChange%
GOOG691.75+1.31.19

Here is an overview of  the steps:

1. Create a "REST Service Connection" "Data Source" in SharePoint Designer.
2. Open the page you want to add your new stock quote in SharePoint Designer.
3. Insert a Data View using the new Data Source you created.
4. Open your page from the web and 'Edit Web Part' on your new Data View Web Part.
5. Modify the XSL to format the data. (Simply cut and paste code below.)
6. Save.

That's it! So let's get started.

1. Create a "REST Service Connection" "Data Source" in SharePoint Designer.
Oooh REST Service Connection....very scary.  Right? Wrong! I've read blogs and blogs on REST and I still can't make heads or tails of it. The more I read, the less RESTful I get. I digress. There are a number available API's out there, but finding the right one took a little hunting. Here is the URL for retrieving a stock quote on Google from Google:

http://www.google/ig/api?stock=goog

To create the REST Service Connection, open SharePoint Designer to you site, select Data Sources, and then click on the Rest Service Connection. A Data Source Properties window will come up. 

1. a. Enter the URL string. 
1. b. Tab out of the field (the stock parameters will appear in the list below)
1. c. Click OK.

You will then see the "api on www.google.com" in the "RSS, REST,  Server Scripts" section of the Data Sources.

See the image below:


Configure REST Service Connection for RSS SharePoint Online Web Part


2. Open the page you want to add your new stock quote in SharePoint Designer.
3. Insert a Data View using the new Data Source you created.



This is what it looks like:
Pretty ugly huh? Not very useful either....Ok, let's clean it up...Save your work and close out SharePoint Designer.


4. a. Open your page from the web and 'Edit Web Part' on your new Data View Web Part.



4.b. Open the XSL Editor




5. Modify the XSL to format the data.

This is last step. You will need to change the XSL in the template section named "dvt_1.rowview". Since this is and even worse text editor since notepad, you'll want to drag and expand the window as large as it will go. (Sorry, no max window icon on this screen).

Replace all the code within this tag

<xsl:template name="dvt_1.rowview">



and BEFORE the close tag


</xsl:template>   (you'll find it right before <xsl:template name="dvt_1.empty">)



Here is the code to replace that section:

<tr>
 <td>
  <table border="0" cellspacing="0" width="100%">
   <tr>
    <td width="25%" class="ms-vb">Symbol</td>
    <td width="25%" class="ms-vb">Last</td>
    <td width="25%" class="ms-vb">Change</td>
    <td width="25%" class="ms-vb">%</td>
   </tr>
   <tr>
    <td width="25%" class="ms-vb">
     <b><xsl:value-of select="finance/symbol/@data"/></b>
    </td>
    <td width="25%" class="ms-vb">
     <xsl:value-of select="finance/last/@data"/>
    </td>
    <td width="25%" class="ms-vb">
     <xsl:choose>
      <xsl:when test="finance/change/@data &lt; 0">
       <font color="#CC0000"><xsl:value-of select="finance/change/@data"/></font> 
      </xsl:when> 
      <xsl:otherwise> 
       <font color="#009900"><xsl:value-of select="finance/change/@data"/></font> 
      </xsl:otherwise>
     </xsl:choose>
    </td>
    <td width="25%" class="ms-vb">
     <xsl:choose>
      <xsl:when test="finance/change/@data &lt; 0">
       <font color="#CC0000"><xsl:value-of select="finance/perc_change/@data"/></font> 
      </xsl:when> 
      <xsl:otherwise> 
       <font color="#009900"><xsl:value-of select="finance/perc_change/@data"/></font> 
      </xsl:otherwise>
     </xsl:choose>
    </td>
   </tr>
   <xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
    <tr>
     <td colspan="99" class="ms-vb">
      <span ddwrt:amkeyfield="" ddwrt:amkeyvalue="string($XPath)" ddwrt:ammode="view"></span>
     </td>
    </tr>
   </xsl:if>
  </table>
 </td>
</tr>



6. Save your work!



That's it. 



Did it work for you? Have any twist or other cool implementations? Share!

Cheers,

Carlos














5 comments:

  1. Hey Carlos,

    This works great. I would like to show multiple rows with each row having a different stock value.

    How can I format it using XSLT?

    I was able to pull the stock value from Google using this URL http://www.google.com/ig/api?stock=MSFT&stock=GOOG&stock=FB

    ReplyDelete
    Replies
    1. Sorry, but I think I tweaked this from another XSL example I found. XSL is Greek to me. :) It's a shame because it would be great if someone could set up some XSL templates to format some of the existing web parts that exist for SharePoint.

      Delete
  2. BTW... Unfortunately, Google no longer supports this API.

    Anyone know of a free service to get stock quote information?

    ReplyDelete
  3. Doesn't Yahoo have a free web service, but they have a bit of a delay.

    ReplyDelete
  4. http://finance.google.com/finance/info?client=ig&q=NASDAQ:GOOG

    ReplyDelete