SharePoint Stock Ticker

29 Jul

A frequent corporate intranet requirement is to show a stock ticker value some where in the master page, or home page.  Doing this using free services is a bit of a challenge, and the ideal scenario is to periodically cache the value into the site, where it can then be easily used without generating a large amount of traffic to the stock value provider.

Yahoo provides a stock service that given the proper URL, with return a comma delimited text result.

For example, try this URL:

http://finance.yahoo.com/d/quotes.csv?s=msft&f=l1c1

 

The ticker symbol, MSFT is self explanatory.  The “l1c1” code says that we want two bits of information:

  • “l1” which is the last sell price
  • “c1” which is the last change value.

Here is a table of available codes for the Yahoo stock data.  You can also put multiple symbols in the URL.

a

Ask

a2

Average Daily Volume

a5

Ask Size

b

Bid

b2

Ask (Real-time)

b3

Bid (Real-time)

b4

Book Value

b6

Bid Size

c

Change & Percent Change

c1

Change

c3

Commission

c6

Change (Real-time)

c8

After Hours Change (Real-time)

d

Dividend/Share

d1

Last Trade Date

d2

Trade Date

e

Earnings/Share

e1

Error Indication (returned for symbol changed / invalid)

e7

EPS Estimate Current Year

e8

EPS Estimate Next Year

e9

EPS Estimate Next Quarter

f6

Float Shares

g

Day’s Low

h

Day’s High

j

52-week Low

k

52-week High

g1

Holdings Gain Percent

g3

Annualized Gain

g4

Holdings Gain

g5

Holdings Gain Percent (Real-time)

g6

Holdings Gain (Real-time)

i

More Info

i5

Order Book (Real-time)

j1

Market Capitalization

j3

Market Cap (Real-time)

j4

EBITDA

j5

Change From 52-week Low

j6

Percent Change From 52-week Low

k1

Last Trade (Real-time) With Time

k2

Change Percent (Real-time)

k3

Last Trade Size

k4

Change From 52-week High

k5

Percebt Change From 52-week High

l

Last Trade (With Time)

l1

Last Trade (Price Only)

l2

High Limit

l3

Low Limit

m

Day’s Range

m2

Day’s Range (Real-time)

m3

50-day Moving Average

m4

200-day Moving Average

m5

Change From 200-day Moving Average

m6

Percent Change From 200-day Moving Average

m7

Change From 50-day Moving Average

m8

Percent Change From 50-day Moving Average

n

Name

n4

Notes

o

Open

p

Previous Close

p1

Price Paid

p2

Change in Percent

p5

Price/Sales

p6

Price/Book

q

Ex-Dividend Date

r

P/E Ratio

r1

Dividend Pay Date

r2

P/E Ratio (Real-time)

r5

PEG Ratio

r6

Price/EPS Estimate Current Year

r7

Price/EPS Estimate Next Year

s

Symbol

s1

Shares Owned

s7

Short Ratio

t1

Last Trade Time

t6

Trade Links

t7

Ticker Trend

t8

1 yr Target Price

v

Volume

v1

Holdings Value

v7

Holdings Value (Real-time)

w

52-week Range

w1

Day’s Value Change

w4

Day’s Value Change (Real-time)

x

Stock Exchange

y

Dividend Yield

 

To use these values in SharePoint, I’ve built a custom feature and timer job, which combined with a list in the root of your site, downloads updated stock values for your symbols every 5 minutes.  You configure which symbols to download information for by adding them to your list.

 

Once you have the data safely in your list, you can show it on any page using a simple data view, or ideally a simple ajax call using jQuery against the lists.asmx web service to save your end-users some load-time.

 

The Solution

First let’s look briefly at how the solution works, then we’ll discuss how to install it, and provide all the downloads at the bottom of the post.

 

The solution is very simple, with a code file for the timer job, and a code file for the feature receiver, which will install our timer job when the feature is activated.

 

image

 

I started with Andrew Connell’s Timer Job code, so the solution will build directly to a WSP from Visual Studio using the custom build targets.  You can also read there about how the feature activation installs the job.  I’ve changed this very little.

 

The actual stock data is downloaded with a trivial function leveraging the System.Net.WebClient as follows:

    <span class="kwrd">private</span> <span class="kwrd">string</span>[] StockData(<span class="kwrd">string</span> ticker)
    {
        System.Net.WebClient client = <span class="kwrd">new</span> WebClient();
        <span class="kwrd">string</span> csv = client.DownloadString(<span class="kwrd">               string</span>.Format(<span class="str">"http://finance.yahoo.com/d/quotes.csv?s={0}&amp;f=l1c1"</span>,ticker));
      client.Dispose();
        <span class="kwrd">return</span> csv.Split(<span class="str">','</span>);
    }

And here is the (somewhat rough) execute function that runs every 5 minutes to update your list:

 

    <span class="kwrd">public</span> <span class="kwrd">override</span> <span class="kwrd">void</span> Execute (Guid contentDbId) 
    {
      <span class="rem">// get a reference to the current site collection's content database</span>
      SPWebApplication webApplication = <span class="kwrd">this</span>.WebApplication;
      SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];

      
      <span class="kwrd">if</span> (contentDb.Sites.Count &gt; 0)
      {
          <span class="kwrd">try</span>
          {
              SPList stockList = contentDb.Sites[0].RootWeb.Lists[<span class="str">"StockValue"</span>];

              <span class="kwrd">if</span>(stockList!=<span class="kwrd">null</span>)
              {
                  contentDb.Sites[0].RootWeb.AllowUnsafeUpdates = <span class="kwrd">true</span>;
                  
                  <span class="kwrd">foreach</span> (SPItem item <span class="kwrd">in</span> stockList.Items)
                  {
                      <span class="kwrd">if</span> (item.Fields.ContainsField(<span class="str">"Symbol"</span>)
                item.Fields.ContainsField(<span class="str">"Value"</span>) &amp;&amp; item.Fields.ContainsField(<span class="str">"Change"</span>))
                      {
                          <span class="kwrd">string</span>[] data = StockData(item[<span class="str">"Symbol"</span>].ToString());
                          item[<span class="str">"Value"</span>] = data[0];
                          item[<span class="str">"Change"</span>] = data[1];
                          item.Update();
                          
                      } 
                  }
                  contentDb.Sites[0].RootWeb.AllowUnsafeUpdates = <span class="kwrd">false</span>;
              }
          }
          <span class="kwrd">catch</span> 
          { 
              <span class="rem">//You should consider adding logging</span>
          }
      }
    }

 

Notice the way we get our list using the content DB, your mileage may vary.  Logging would also be a nice addition.

 

Install

 

In addition to the solution, I’ve provided a WSP for those that would like to simply install this.  Here is how you would use it:

 

Step 1:

Create a new list in the root of your site, called “StockValue”

New SharePoint List

 

Step 2:

Add columns to the list.  All strings.  “Symbol”, “Value”, and “Change”.

image

“Single Line of Text” is the type for all three, everything else left as default.

image

 

Your list should look like this:

 

image

 

Step 3:

Add your ticker symbols to the list, by creating a new row and populating the Symbol field.  You can optionally populate the Title field.  You can add 1 or as many as you would like.

image

 

Step 4:

Install the solution.  The WSP (SharePoint Solution) is available for download at the bottom of the post.  You will install this as you would any other WSP.  From a server on the farm use STSADM to add and deploy.

C:&gt;stsadm -o addsolution -filename c:stefangordon.stockvaluejob.wsp
C:&gt;stsadm -o deploysolution -name stefangordon.stockvaluejob.wsp -immediate -allowgacdeployment

Give the farm a minute or two to deploy the solution.

 

Step 5:

Active the feature in your site collection, from Site Settings->Site Collection Features

This would be the site which has the list from step 2 in it, at the root, called “StockValue”.

image

 

Step 6:

Wait 5 minutes, for the job to run and update your list.  It should then contain the stock values.  If it is after-hours, the change amount will always be 0.

 

image

 

You can go check the status of the job in Central Admin, to see if it has run:

image

 

Downloads

Download the Visual Studio 2008 solution.

Download the compiled WSP.

Tweet about this on TwitterShare on Google+Share on FacebookShare on RedditEmail this to someoneShare on LinkedInShare on StumbleUpon

2 thoughts on “SharePoint Stock Ticker

  1. Hey Scott,

    We had downloaded and installed the web part in our SharePoint 2007 farm. We are now upgrading it to SharePoint 2010. I was wondering if you have the source files somewhere so we can upgrade the visual studio solution and rebuild it if needed. I tried clicking the link above but it gives me an error.

    Thank you and appreciate all your hard work and time!

  2. Have you ever seen something similar to this for SP Online. I am setting up a Stock area on our page and we want to list 5 competitors also. I have an app from the SP Store but the CEO wants it as a list not individual Apps for each stock quote.

Leave a Reply

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