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.

 

 

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:

 

    private string[] StockData(string ticker)
    {
        System.Net.WebClient client = new WebClient();
        string csv = client.DownloadString(

               string.Format("http://finance.yahoo.com/d/quotes.csv?s={0}&f=l1c1",ticker));

        client.Dispose();
        return csv.Split(',');
    }

 

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

 

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

      
      if (contentDb.Sites.Count > 0)
      {
          try
          {
              SPList stockList = contentDb.Sites[0].RootWeb.Lists["StockValue"];

              if(stockList!=null)
              {
                  contentDb.Sites[0].RootWeb.AllowUnsafeUpdates = true;
                  
                  foreach (SPItem item in stockList.Items)
                  {
                      if (item.Fields.ContainsField("Symbol") && 

                          item.Fields.ContainsField("Value") && item.Fields.ContainsField("Change"))
                      {
                          string[] data = StockData(item["Symbol"].ToString());
                          item["Value"] = data[0];
                          item["Change"] = data[1];
                          item.Update();
                          
                      } 
                  }
                  contentDb.Sites[0].RootWeb.AllowUnsafeUpdates = false;
              }
          }
          catch 
          { 
              //You should consider adding logging
          }
      }
    }

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"

 

Step 2:

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

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

 

Your list should look like this:

 

 

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.

 

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:\>stsadm -o addsolution -filename c:\stefangordon.stockvaluejob.wsp

C:\>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".

 

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.

 

 

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

 

Downloads

Download the Visual Studio 2008 solution.

Download the compiled WSP.

This morning I threw together some quick instructions for a client on customizing the Core Results Web Part and adding additional fields to the MOSS Search Results.  I hadn't planned on posting it, but as I already had it done I thought it might help someone else.  Certain text blacked out to protect the innocent!

 

Here we will be customizing the People Results page by adding a new link to the results, which passes the account name of the user as a query string parameter.  This process would be the same to add any field to any of the results pages.

 

All the results rendering for your main search results is in the core results web part, so here I do a People search, edit the page and modify the core results part:

The part passes a bunch of data to the XSL we provide for rendering, but by default the field I would like to use, AccountName isn't passed.  You can pass anything that a managed property exists for in the Managed Properties section of the Shared Service provider (under Search Settings).

 

If you look at the metadata property mappings, there is an Account Name already defined and mapped to the AD import field "People:AccountName(text)".  This is the field I'll add to my search results.

Note: If you add a new field here, You'll need to do a full search crawl before it shows up.

So back in our Search Core Results edited web part, we choose "Selected Columns" under query options, and add this column into the mix:

You'll want to paste these snippets into an XML or text editor when you modify them, as the window that pops up is useless.  You'll add one more entry to the list as shown below, for AccountName (case sensitive here)

Paste that back into the web part property, and go edit the XSL property:

The XSL in there is relatively small and manageable.  You'll find a template called "Results" which is the actual result line-item rendering.  It has several div's it renders out, Title, Description, srch-Metadata, etc. 

 

The Search Metadata div is where they fit the URL's and such at the bottom of each entry, so it is where we want to add our link today.  You could rewrite the entire results rendering if you'd like. 

I did a simple case that just tacks the URL built from our new column on the next line.  See the two additions in red I made to the XSL.

Note: Confusingly, the XSL is case sensitive on these columns as well, except it's a different case, they have to be all *lower* case.  Example:  In the columns XML I put "AccountName" but when I use it in the XSL, I must use "accountname".

 

My changes are below in red outline, they are as follows:

1. Added new variable called account based on our accountname column that was passed in.  I will use this variable in string concatenation to generate my URL.
2. Used that variable (As well as the column) down below to add a new link in an HTML <a> tag.

 

When you are done editing, paste your XSL back into the XSL property for the web part (hopefully yours is prettier than mine) and you'll get your new results:

 

If it tells you it can't render the web part after you hit apply, something is wrong with your XSL.

 

Note: For more advanced XSL editing, you may want to use a real XSL editor such as Oxygen XML, which will let you test, debug, and preview your results.  To do this you'll need to get a sample of the XML to work with, which you can do by pasting an "Identity" XSL into the XSL property, viewing the page source, and capturing your XML.

I'm constantly digging around to find a copy of an Identity XSL to use in my SharePoint development.  As so much of SharePoint can be modified through XSL customizations, it's important to be able to get a copy of the source XML so you can go work in a proper XSL Debugger (such as Oxygen XML) to create your XSL files.

 

To do that, I use this identity XSL (for example, as my search results XSL) and it causes SharePoint to print out the raw source XML into the page source, where I can copy it and use it.

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <!-- IdentityTransform -->
    <xsl:template match="/ | @* | node()">
        <xsl:copy>
            <xsl:apply-templates select="@* | node()"/>
        </xsl:copy>
    </xsl:template>

</xsl:stylesheet>

Before you can use Server 2008 (r1 or r2) as a Web Front End in your SharePoint farm, you'll need to enable some new roles.

 

With a new release of Server, and a new release of SharePoint on their way, I will have many posts up here regarding installations, so for the sake of normalization, I'll post a guide to adding these roles here, and refer to this post in future articles.

 

When you first start your Server 2008 box, you should get a panel for 'Initial Configuration Tasks'.  You may choose to 'Add Roles' from here, or launch Server Manager and add roles from there.

 

 

You'll get the 'Add Roles Wizard', and you'll need to choose 'Web Server (IIS)'

 

 

Accept the prompts for prerequisites:

 

Add 'ASP.Net' and 'Windows Authentication' and accept prerequisites prompts again:

 

 

Finish the wizard, and you should get an 'Installation Succeeded' display:

 

Congratulations, you now have IIS and ASP.net working, so you can install SharePoint.

In a decent sized SharePoint farm there are *many* moving pieces.   At least 5 servers, probably 10 nics, SAN's, SQL, MOSS, IIS, Load balancers and Kerberos issues.  It can be difficult to figure out what is causing performance problems sometimes.

There are many good documents out there, especially the whitepaper on SharePoint Performance Optimization from MS IT, and simply familiarizing yourself with the common issues will help you identify many problems immediately.

That said, sometimes you can't get your hands on the hardware and the problem isn't obvious.  Assuming you've verified SharePoint configuration and scanned the ULS logs first, I typically ask an Admin to pull these WMI Counters for me while generating load -- They are primarily on the SQL end, with the exception of checking NIC performance all around.  I focus on that first, because 75% of the time I find the disk-subsystem or the NIC/Network is to blame. 

Each one has a recommended range to look for.  These were pulled from a few MS documents.  Hope they help you in the future identifying an infrastructure problem.

 

Counters to monitor on SQL Server:

 

Logical Disk: Average Disk sec/Read (Read Latency).

This counter indicates the time it takes the disk to retrieve data. On well-tuned I/O subsystems, ideal values are 1-5 ms for disk containing sql logs, and 4-20 ms for disks containing data (ideally below 10 ms).

Logical Disk: Average Disk sec/Write (Write Latency).

This counter indicates the time it takes the disk to write the data. On well-tuned I/O subsystems, ideal values would be 1-5 ms for log, and 4-20 ms for data (ideally below 10 ms).

Logical Disk: Current Disk Queue Length.

For this counter, lower values are better. Values above 20 may indicate a bottleneck

 

On Web Front ends and SQL:

 

Processor: % Processor Time: _Total.   

This counter should be kept between 50 percent and 75 percent.

System: Processor Queue Length: (N/A).

This should be below two times the number of CPU’s.

Memory: Pages/sec: (N/A).

Monitor this counter to ensure that it remains below 100.

Network: Network Queue Length counter.

Monitor under load, and watch for spikes.