Edited by Nina Miller
Figure 1 - Flashing Stock Alert using the Basic Stamp II and .NET
Introduction
In the stock market, a stock price can go for or against you at any time. Traders need to be wary of price changes so that they don't lose a lot of money. One method of warning the trader about price fluctuations is a flashing alert on the screen. However, let's say for argument's sake that the trader is not at his or her computer. What if the trader is across the room behind the computer screen? How will he or she know that the alert has been tripped? One way to show an alert is to sound an alarm from the PC and turn up the volume on the sound card. On the other hand, this technique may piss off all the other traders (and you don't want to piss off a trader). Another idea is to extend the visible signal of the stock alert beyond the computer screen. Thus we have created the flashing stock alert.
Design
The flashing stock alert consists of two components:
(1) The .NET solution that allows traders to set alerts in their favorite PC program-Microsoft Excel. (2) The Parallax Basic Stamp II Circuit containing the Light Emitting Diode (LED) flashers used to alert the user. Here is how the application works: The trader types in the symbols of the stocks s/he wishes to monitor in the stocks column of the excel spreadsheet. The prices of these stocks are then brought into the price column from a website that provides quotes and is updated every second. The quote is also checked against a high and low value in the columns adjacent to the price column provided by the trader. If the price exceeds the high value column or falls below the low value column a signal is sent over the serial port to the BASIC Stamp Board to alert the trader with a flashing LED.
Figure 2 - Block Diagram of System Design for Flashing Stock Alert
Working with an Office Project
The .NET code utilizes the Office Project capability in Visual Studio 2005. If you click New -->Project and choose Office, you can actually create a ready-made Excel project in which you can enter C# code to interact with an Excel spreadsheet. Pretty cool, huh? Now you can do all your coding for Excel in C# class files representing each of the different components of the COM model (i.e. sheets and workbooks). You still have to deal with the old Excel object structure, but it beats coding for Excel through VBA. You can even drop .NET components right onto the Excel SpreadSheet! For example, you can drag a Button from the Visual Studio onto the Excel spreadsheet in your project and hook an event handler to it. (That feature pretty much blew me away when I tried it.)
Figure 3 - Utilizing the Office Project Capability in Visual Studio 2005
The .NET Code
When talking to Excel from .NET you need to work with Ranges. Ranges can be any set of cells within the Excel Spread Sheet defined by the corners of the range (e.g. A1, B6). Programming .NET to manipulate Excel is not as forgiving as one would expect because you are still going through the painful COM layer. However, once you get the hang of it, you'll soon be populating and reading cells in Excel with C# code.
The application design on the .NET side centers around three main classes: the Worksheet we are working in, a stock scraper for getting stock values from the web, and a serial port alert class that handles all the nitty gritty details of sending alerts to the Basic Stamp through the serial port. The program works on a 1-second thread timer contained in the work sheet. Every second, the timer event is triggered and PopulateStockPrice is called to populate the current stock prices of all the available symbols contained column A. PopulateStockPrice also tests the high and low thresholds contained in columns C and D and sets off the alert if the price is outside the bounds of the constraints.
Figure 4 - UML Design illustrating the 3 classes for our stock Alert Program
Let's examine the PopulateStockPrice method in the Sheet1 class code in Listing 1 since this is where all the action takes place. The first thing we do is to get a list of stock symbols from the spread sheet in column A using the Range object in the excel sheet. Then we loop through each of these symbols and scrape the current price of the stock off a website using the StockScraper. Finally, we get the high and low constraints for the current symbol and test them against the current price using the SerialPortAlertSignal class. If they are outside the bounds of the constraints, we send a signal to through the serial port to the Basic Stamp.
Listing 1 - Populating Stock Prices in Excel and Checking Them Against Constraints
private void PopulateStockPrices()
{
// get a list of all the symbols in column A
object[,] values = (object[,])this.Range["A2", "A50"].Value2;
// start in the second row, below the title row
int rowcount = 2;
// loop through all the symbols in column A
foreach (string symbol in values)
{
// if we reached a blank symbol, then we reached
// the end of the symbol list, break
if (symbol == null || symbol.Length == 0)
break;
// use the stock scraper to get the stock value
// for the current symbol
string val = _scraper.GetStockValue(symbol);
// set column B to the price received from the web
this.Cells[rowcount, 2] = val;
// get the high price value constraint from column C
string high = GetHigh(rowcount);
// test the high value against the current price
// and signal an alert if its outside the constraint
_alertSignal.TestHigh(val, high);
// get the low price value from column D
string low = GetLow(rowcount);
// test the low value against the current price
// and signal an alert if its outside the constraint
_alertSignal.TestLow(val, low);
// go to the next row
rowcount++;
}
} |
Screen Scraping Quotes:
There are many ways to get stock information from the internet. The best way to get the most current quotes (real-time) is directly from a paid service such as Reuters. Unfortunately, these services tend to be expensive. For the purpose of this article, we scrape the screen of a web site that displays quotes. The problem with screen scraping is threefold: (1) quotes tend to be delayed as much as 15 minutes and not real-time. (2) it's not necessarily the quickest way to retrieve a quote because you have to parse through an entire web page (3) you have no control over the content of the page, so if the page structure changes, your screen scraper may not work anymore. The nice thing about screen scraping quotes, however, is that it is free and good for demonstration purposes. Below is the method that allows us to scrape a quote using our StockScraper class shown in listing 2. Basically, this method goes to the server URL providing the quote and extracts the web page into a string. Then we scrape through the page looking for the quote based on known tags that point to the quote. Remember that you have no control over these tags and they can change at any time. If they do change, simply rewrite the GetStockValue method.
Listing 2 - The GetStockValue method in the ScreenScraper class
public string GetStockValue(string stock)
{
//Create a HttpWebRequest object for the server search URL
HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(String.Format("{0}{1}&d=e", ServerURL, stock));
// retrieve the response web page we want to scrape and put it into a string
HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);
string res = strm.ReadToEnd();
// find the price of the stock on the page by searching for it
// through a combination of known tags and the stock symbol
string quotePrefix = "<big><b>";
int quoteIndex = res.IndexOf(quotePrefix, res.IndexOf(String.Format("({0})", stock), res.IndexOf("setPortfolioBehavior()")));
quoteIndex += quotePrefix.Length;
string result = res.Substring(quoteIndex, 15);
// match the stock price using a regular expression for the
// numeric form #######.#######
Regex expression = new Regex(@"[0-9]+\.[0-9]+");
Match match = expression.Match(result);
result = result.Substring(match.Index, match.Length);
return result;
} |
Sending Alerts over the Serial Port
Now we come to the fun part: talking to the hardware to alert the trader that the stock has tripped a boundary condition. We can use our SerialPortAlertSignal class to communicate through the serial port to the board. The serial port uses a protocol known as RS232 to communicate data sent back and forth through the port. When we construct the SerialPortAlertSignal object, we take the opportunity to initialize our serial port settings. Listing 3 shows the Initialize method of the SerialPortAlertSignal class. For our purposes we set up the communication to be 9600 baud (9600 bits/sec) , 8 bits of data, 1 stop bit and no parirty. The 8 bits of data indicate that each frame of data we send out will contain 8 bits of information. The stop bit tells the serial port chip when the data in that frame has completed. Parity is used to check errors in the data, but we don't use it here.
Listing 3 - Setting up the Serial Port for (9600, 8, 1, n) baud communication at
| /// <summary>
/// Data will be sent out on COM1: (9600, 8, 1, N)
/// </summary>
private void Initialize()
{
// set the com port we are using from the PC
_serialPort.PortName = "COM1";
// set the baud rate (the rate that data is sent and received in bits/sec)
_serialPort.BaudRate = 9600;
// parity bit for error detection purposes: even, odd, or none
_serialPort.Parity = Parity.None;
// set stop bit to one (used to separate the data into frames)
_serialPort.StopBits = StopBits.One;
// set number of bits in each frame of data
_serialPort.DataBits = 8;
// no handshaking used
_serialPort.Handshake = Handshake.None;
} |
The TestHigh and TestLow methods of the SerialPortAlertSignal class determine if we tripped our constraints in the high and low price range respectively. If either of these methods tests true, we internally call the SetAlert command to send a character through the serial port to tell the Basic Stamp which LED to light:
Listing 4 - Testing stock prices to determine if we light the LED Alerts
public void TestHigh(string stockVal, string high)
{
// convert the string price values to doubles
double val = Convert.ToDouble(stockVal);
double highVal = Convert.ToDouble(high);
// test if our price is higher than the constraint
if (val > highVal)
{
// set green for prices below threshold
SetAlert("G");
}
}
public void TestLow(string stockVal, string low)
{
// convert the string price values to doubles
double val = Convert.ToDouble(stockVal);
double lowVal = Convert.ToDouble(low);
// test if our price is lower than the constraint
if (val < lowVal)
{
// set red for prices below threshold
SetAlert("R");
}
} |
The SetAlert method of the SerialPortAlertSignal class talks directly to the serial port through the System.IO.Ports.SerialPort class which is part of the .NET framework. This class makes it quite easy to both send and receive data via RS232 communication over a serial port on a PC. (If your computer only has USB, you may need to get a serial port adapter to do this experiment). SetAlert takes advantage of the Write method in the SerialPort class to write a string out onto the serial port.
Listing 5 - Setting Alerts through the Serial Port to the Basic Stamp
public void SetAlert(string r)
{
// open the serial comm port
_serialPort.Open();
// send the string over the serial port
_serialPort.Write(r);
// close the serial comm port
_serialPort.Close();
} |
In our design, we use a character to indicate which LED we are lighting. "R" indicates that we want to light the red LED and "G" indicates that we want to light the Green LED. A character byte sent over the line is a binary representation in the form of a series of high and low signals. For example "G", which is ASCII 71 (or hex 0x47) would be represented as 01000111 and the signal would look like figure 5 over the serial port. Note that the low horizontal segments are binary 0 and the high horizontal segments are binary 1. These segments correspond to high and low voltages on the pins of the serial port.
Figure 5 - Logic High-Low Diagram Illustrating the ASCII "G" character sent over the RS232 serial port
The Basic Stamp
Parallax provides a microcontroller kit called the Board of Education (BOE) which allows you to easily control the Basic Stamp II microcontroller using the BASIC language. This kit fits our hardware requirements for this project. The circuit shown in figure 6 connects our LEDs to three of the BASIC Stamps ports so that we can control their state by sending signals to the serial port. 220 ohm resistors are added in between the port and the LED to limit current going through the LED (so we don't burn them up). We will take advantage of two features of the Basic Stamp to create our stock alerts. The first feature is the ability to receive command on the serial port and the second feature is the ability to toggle the logic state of ports 1,3 and 15 to light or extinguish the LEDs.
Figure 6 - Stock Alert Circuit hook up on the Board of Education
The code needed to program the Basic Stamp is shown in listing 6. The SERIN command waits for the data from the serial port and when it receives the data, it places it in the command variable. The Select statement chooses the appropriate subroutine to call based on the command received. Based on the command, the appropriate port will be set either high or low to light or extinguish the LED.
The Basic Stamp language has a command called TOGGLE that allows you to toggle the state of the port. If your previous state is high on the port, then TOGGLE sets the state to low and if the previous state is low on the port, then TOGGLE sets the state to high. The TOGGLE command, which is called once every second, gives us the visual effect of a flashing LED.
Listing 6 - Basic Stamp II BASIC Code for Receiving Commands on the Serial Port and Lighting LEDs
Conclusion
As embedded system hardware becomes more accessible to the developer, more and more applications will take advantage of its powerful reach to the outside world. Connecting the Parallex BOE to your PC is an easy and fun way to implement hardware from your C# programs. Although Microsoft provides a Robotics SDK to do some hardware control in Visual Studio, it's easy enough to get started without it. Simply plug the BOE board into the PC through the serial port and start coding. Stay tuned for more practical applications with C#, .NET and the Board of Education from Parallax.
Suggestions: A fun way to extend this project, would be to adapt it to the pocket PC. Perhaps we will publish an adaptation in the near future.