Article Author: Purvi Vora
Introduction
People invest part of their wealth in stocks for the future. The big time investors use sophisticated tools for monitoring their investments in stocks. For such people, this may be a primary or major source of income and thus they spend additional money in obtaining tools for monitoring or obtaining professional help in tracking their investments. The Related Links Section has links to several programs that can be used for the technical analysis of stocks that help in making trade decisions for a fee.
However, small time investors generally invest money in stocks on recommendation by a friend or a relative. Such people over a period of time may even forget such investments in stocks. The stock prices are subject to fluctuations and may even drop down to a negligible value in a short duration. The prices may even shoot up to astronomical figures over a period of time. If timely information is made available to the investor, he or she can control one’s portfolio profitably. Thus, an appropriate Portfolio Manager that is convenient to use is desired.
Sometimes stock brokers and repositories provide such facilities of portfolio management to their customers. Such services may be available free of charge or at a premium depending on the breadth of facilities offered to the investor. A small investor may not avail of such facilities, hence the creation of the Personal Portfolio Manager (PPM).
In this case study, we develop a mobile PC based application that will help even a small investor track their investments in the stock market easily. Before we discuss the application, we will take a look at the system requirements for developing the application as well as the installation procedures for installing and running the application. we will then dive into the database design and the actual coding of the application.
System requirements
Developing a mobile PC based application requires the .NET Compact Framework. When you install Visual Studio .NET 2003, the compact framework (.NET Framework version 1.1) gets installed on your machine. VS.NET provides a wizard called the Smart Device Applications for developing mobile applications. These run on Pocket PCs and Windows CE .NET platforms.
Visual Studio .NET 2003 provides two modes for testing your Smart Device Application:
- Using the emulators
Visual Studio .NET 2003 provides emulators for both the Windows CE .NET platform and Pocket PC 2002 platform. The emulators provided in Visual Studio .NET are an actual implementation of the Pocket PC (or Windows CE .NET) operating system that’s compiled to run on the x86 platform. VS.NET also supports hardware emulation.
- Using an actual device
For applications that make use of specific hardware features (such as Bluetooth), Visual Studio .NET 2003 allows you to test your application on a real device. For testing on a real device, you need to establish a connection with the device (via a USB or serial cable). You also need Microsoft ActiveSync 3.5 so that the applications can be copied onto the device for testing.
Compiling and Installing the Sample Code
The entire application is available for download from the ASPToday website. After downloading it, simply unzip the downloaded file to the desired folder and open the VS.NET solution file in VS.NET IDE. You may now rebuild the project and deploy it on an emulator to run the application. Note that you will need an Internet connection for using some options.
If you decide to create the project by yourself as we cover it, select the Smart Device Application wizard in VS.NET and add the provided .cs files to the project. The application uses an external web service to read the stock quotes from NYSE.
The external web service is provided by xmethods.com and is consumed by our application. You will need an active Internet connection to retrieve the stock quotes.
To include the reference to the external web service in your project, add a web reference to the following URL:
http://services.xmethods.net/soap/urn:xmethods-delayed-quotes.wsdl
To add a web reference, right click on the Add Web Reference option in Solution Explorer . Clicking on it you get a screen as shown in Figure 1.
Figure 1. Adding a Web Reference Step 1
Input the hyperlink above into the URL field and click the Go button. Once the service is located, you will see the screen shown in Figure 2. It may take several minutes to locate the service depending on the speed of your Internet connection.
Figure 2. Adding a Web Reference Step 2
Click on the Add Reference button to add the reference to the web service to your project. The net.xmethods.services namespace will now be available to your program code. This namespace contains a class called netxmethodsservicesstockquoteStockQuoteService that defines the getQuote() function which takes the stock symbol as a parameter and returns the current price for that stock.
Running the Application
In order to run the application in an emulator, follow the steps listed below:
- Open the project in VS.NET 2003.
- Build and deploy the solution.
- A choice of emulators and devices will be presented to you:
- The Pocket PC 2002 Emulator emulates a real device running the Pocket PC 2002 platform.
- The Windows CE.NET Emulator emulates a real device running the Windows CE.NET operating system.
- The Pocket PC Device is the real device attached to your computer.
- The Windows CE.NET Device is the real device attached to your computer.
- Select the desired emulator or device for deployment and run the application once it is deployed.
- When you run your application for the first time, Visual Studio .NET copies all the relevant .NET Compact Framework assemblies to the emulator.
- You get the application Main Form on the screen once the application is loaded in the emulator. If you do not see the Main Form, you may have to locate and launch the application.
- Select the desired menu options to test the application.
Mobile Based Portfolio Manager Overview
Use of mobile phones has become quite common these days. A portfolio manager application developed for a mobile device that can fetch the latest stock prices from the stock exchange would certainly help the investor in better tracking their portfolio. The application developed in this article provides a simple to use user interface for recording the trades executed by an investor. The application captures the stock symbol, trade quantity and price for each trade from the user and stores the data in a SQL Server CE Edition database on the user’s mobile device.
The Portfolio Manager allows the user to obtain the latest stock quotes from NYSE. For this, a third-party web service that retrieves the delayed quote from the NYSE for the specified symbol is used. As each such call may take substantial time to execute and there could be several scrips in the portfolio, there needs to be a way to make concurrent calls to the web service to improve the application response time. The application creates a thread for each record in the database (each scrip) and makes a web service request in a new thread. The thread programming for .NET CE poses a few challenges since the not all the functionality of the desktop version of the .NET framework has been ported to the compact version of the Framework. This is addressed later in the code discussions.
The application also computes the net value for each scrip by multiplying the quantity-on-hand by the latest stock quote. The cumulative total for all the scrips is also displayed to the user. This is the current net worth of the portfolio.
The application allows the user to maintain their portfolio by deleting any erroneous or out of date entries in the database. Over a period of use, when stocks are bought and sold, the net quantity of some scrips may become zero. Such records need to be deleted from the database. The application allows the user to delete any record stored in the database.
Whenever a new trade is entered in the application, the program searches the existing record for the input scrip in the database. If a record is found, the current input quantity is added to or subtracted from the existing quantity. This will be the net stock quantity on hand. The value of the existing stock and the value of the current trade are then calculated by multiplying the quantity and the corresponding price. The existing stock value is then adjusted for the value of the current trade to obtain the new net value for the stock. This net value divided by the net quantity on hand giving the average purchase price for the stock. This price can be important to the trader while making further trading decisions. This may be considered as an indicative price at which the balance stock may be liquidated without incurring any loss. These calculations are explained in depth later in the article.
The application does not maintain a history of transactions as this would consume a lot of memory that is already scarce on a mobile device. However, the last trade quantity, its price and the type of transaction (Buy or Sale) are maintained.
Creating the Personal Portfolio Manager
This section will focus on the design and coding of the Personal Portfolio Manager. we will start by designing a database to hold the scrip data and then move onto the application code itself.
Database Design
The PPM uses SQL Server CE for storing the portfolio data. The database contains a single table InvoiceTransaction as shown below:
| Sr. No | Field Name | Data type | Size |
|---|---|---|---|
| 1 | StockSymbol | nvarchar | 25 |
| 2 | Quantity | int | |
| 3 | Price | float | |
| 4 | CurrentPrice | float | |
| 5 | LastTradeQty | int | |
| 6 | LastTradePrice | float | |
| 7 | TypeOfTransaction | nchar |
If we used the usual database normalization techniques, we would probably create two tables, a master table containing stock code and the full name of the stock and a transaction table for recording each transaction. However, this would consume more space in the limited memory available on a mobile device. In the above design, we decided not to store the full company name and information on each trade. Rather than storing each trade’s information, we compute the average purchase price and the balance quantity and store these in the database.
Each record in the table stores information about the last entered trade, the balance quantity and the average price of the scrip. The average price for the current scrip is computed based on the cumulative value of all the trades for a particular scrip.
After each trade entry, we modify the Quantity and the Price fields. These fields respectively specify the balance quantity on hand and the average scrip price after taking into considerations all the earlier trades on this scrip.
Calculating the average price
In this section we will provide an overview with some samples on how the average price calculation is done.
Consider a trade where the investor buys 100 IBM stock at $75. In this case, we store the average price = $75 and quantity =100 in the database. Next, the investor sells 20 stocks of IBM at $90. The net quantity on-hand is now 80. we now compute the average purchase price for the balance of the stock quantity using following calculation:
| The net quantity at the time of purchase | = Σ quantity |
|---|---|
| The net quantity after sale | = Σ (quantity – quantity sold) |
| The average purchase price at the time of purchase | = Σ (quantity * price)/ Σ quantity |
| The average purchase price after sale | = Σ (quantity purchased * purchase price) – (quantity sold * sale price) / |
| Σ (quantity-on-hand – quantity sold) |
Thus, in the above example
| Net Quantity on-hand | = 100 – 20 = 80 |
|---|---|
| Average purchase price ($) | = ((100 * 75) – (20 * 90))/ (100-20) |
| = (7500-1800) / 80 | |
| = 5700/80 | |
| = 71.25 |
The following values for the net quantity and average purchase price would be stored in the database:
| IBM | |
|---|---|
| Net Quantity | 80 |
| Average Price ($) | 71.25 |
Consider another transaction for IBM whereby 40 shares are bought at $80.25
| Then the net quantity | = 80 + 40 = 120. |
|---|---|
| And the average purchase price ($) | = ((80 * 71.25) + (40 * 80.25))/ (80+40) |
| = (5700 + 3210) / 120 | |
| = 8910/120 | |
| = 74.25 |
The following figures would now be stored in the database:
| IBM | |
|---|---|
| Net Quantity | 120 |
| Average Price ($) | 74.25 |
The value in the price field indicates the price at which future trades may be done without incurring a loss. Calculating and storing the stock’s average price this way helps the investor in making quick decisions while doing further trades on any given scrip.
Besides the average price and the balance quantity fields, the database stores the last trade price, trade quantity and the type of trade. While trading, the investor can refer to this data to obtain information about the last trade done on this scrip. Note that the last trade price may substantially differ from the above calculated average price. Knowledge of both last trade price and the average price greatly helps the investor in taking better decisions.
The Application Code
In this section we will go through in detail the development of the Personal Portfolio Manager. When you launch the application in the Pocket PC 2002 Emulator, you will see the screen as shown in Figure 3
Figure 3. Main Form Screen
In the f orm’s Load event of the main form, we create the SQL Server CE database. we also added a reference to the System.Data.SqlServerCe namespace in order to make use of the database functionality from within my code. The application database is stored in the My Documents folder under the name ppm.sdf .
The Pocket PC 2002 follows the Uniform Naming Convention (UNC) path to for accessing the database file. In the UNC you put " \ " instead of " " as you normally do in Windows. For example, if you have a folder named My Folder in My Documents then the UNC path would be \My Documents \ My Folder .
The CreateDatabase() function checks if the database already exists due to an previous run of the application.
if(!File.Exists("\My Documents\ppm.sdf"))
If the database exists, we use it. If the database does not exist, we create the database as follows:
SqlCeEngine sqlceengine= new SqlCeEngine
("Data Source=\My Documents\ppm.sdf");
sqlceengine.CreateDatabase();
Next we obtain a connection to the database using the singleton class Util . we use a Singleton class here to ensure that only one instance is permitted in the program code. Singletons are often used to control access to resources such as database connections or sockets.
Util util = Util.GetInstance();
con = util.GetConnection();
The database is created by creating a SqlCeCommand object and executing a create table statement:
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE TABLE InvoiceTransaction (
StockSymbol nvarchar(25),
Quantity int,
Price float,
CurrentPrice float,
LastTradeQty int,
LastTradePrice float,
TypeOfTransaction nchar)";
cmd.ExecuteNonQuery();
Once the database is created, the user is provided a choice of options:
- To enter a new transaction
- To delete an existing record
- To evaluate the net worth of your portfolio.
Clicking on the Exit menu option terminates the application. Note that terminating the application does not delete your data or the database. The database is persistent and all the recorded entries in the database will be available the next time the application is run.
Transaction Entry Screen
Selecting the Enter Transaction menu option displays the screen shown in Figure 4.
Figure 4. The Enter Transaction Screen
Here you can enter a stock symbol, trade quantity, trade price and its type on this screen. In the LostFocus event of the Stock Symbol text field ( txtName_LostFocus() method in the TransactionMaster.cs file), we check for an existing record for the currently entered scrip code in the database.
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from InvoiceTransaction where StockSymbol=’" + txtName.Text.ToUpper()+"’";
If the record exists, we display the record in the label field below the Accept button.
SqlCeDataReader rd = cmd.ExecuteReader();
if(rd.Read())
{ string str = "Buy"; if (rd.GetString(6)=="S") str = "Sale"; lbl_currentStock.Text="Current " + "Stock = "+ rd.GetInt32(1).ToString() + "nLast Trade : " + str + "nQuantity= " + rd.GetInt32(4).ToString() + " Price= " + rd.GetFloat(5).ToString();
}
The label is used to show the balance quantity (stock-on-hand) for the current scrip and its last recorded trade.
After entering the data, the user clicks the Accept button to store the data in the database. Clicking the Accept button first validates the input data and then calls the SaveData() method to update the database. For validation of the quantity field, we define a IsNumber() method. For validating the price field, we use the IsRealNumber() method. Both IsNumber() and IsRealNumber() methods use regular expressions to validate the input string.
The definition of the IsNumber() method is shown below. It uses the Regex class to define the format string for the validations and then performs the test against the argument passed returning a Boolean result.
public bool IsNumber(String strNumber)
{ Regex numberValid=new Regex("[^0-9-]"); return !numberValid.IsMatch(strNumber);
}
After validating the input data, the program retrieves the record for the current scrip. If the record exists in the database, it is updated to account for the new trade. If the record for the current scrip does not exist in the database, it is created and inserted in the database by running the appropriate SQL command.
Before inserting the record, the program checks for the number of records in the database
cmd = con.CreateCommand();
cmd.CommandText = "select count(*) from InvoiceTransaction";
If the record count exceeds the maximum count defined in MAX_SCRIPS constant, the program prints an appropriate message to the user and does not store the record. we restrict the number of stocks to MAX_SCRIPS in the portfolio just so as to conserve memory space and thereby cooperate with other applications loaded on the mobile device. Over a period of use, the database may contain many invalid or out of date entries such as scrips having balance quantity equal to zero. The user should periodically clean up the database to constrain the database size by using the Delete option provided in the application.
Clicking on the Close button closes the transaction form and returns the user to the main form.
NetWorth Screen
Clicking on the Net Worth menu option opens the NetWorth screen as shown in Figure 5.
Figure 5. The Net Worth Screen
The Net Worth screen displays all the records in the database. For each record, the stock symbol, the balance quantity and the average price is displayed. The current price and the amount columns initially display zero for each entry. The user has to request the application to retrieve the current prices by clicking on the Get Quotes button. Note that the device must be connected to the Internet in order to retrieve the prices from the NYSE.
In the Form’s Load event handler defined in NetWorth_Load() method in NetWorth.cs file, we create a DataTable and add the desired columns to it.
myTable = new DataTable("InvoiceTransaction");
myTable.Columns.Add("SCRIP");
myTable.Columns.Add("QTY");
myTable.Columns.Add("AVG");
myTable.Columns.Add("CURR.PRICE");
myTable.Columns.Add("AMT");
DataRow myRow = myTable.NewRow();
myTable.Rows.Add(myRow);
After initializing the column headings, the program calls the FillGrid() method to populate the grid with the data from the InvoiceTransaction table. The program selects the data by creating the appropriate SqlCeCommand object to query the table.
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "Select StockSymbol, Quantity, Price from InvoiceTransaction";
We use DataReader to read the data from the underlying table.
SqlCeDataReader rd = cmd.ExecuteReader();
We then populate the grid by iterating through all the records in the DataReader .
while(rd.Read())
{ //adding the records in DataTable dataRow drow = myTable.NewRow(); drow["SCRIP"]=rd.GetString(0); drow["QTY"]=rd.GetInt32(1); drow["AVG"]= rd.GetFloat(2); drow["CURR.PRICE"]=0; drow["AMT"]=0; myTable.Rows.Add(drow);
}
The grid is formatted by calling the SetColumnProperties() method which sets the appropriate attributes for the desired grid column.
The data displayed in each row indicates the balance quantity and the average price of the specified stock. While making trade decisions, the average price gives an indication of the price at which stocks may be sold without incurring a loss, the user may be interested in knowing their last trade for the given scrip. Clicking on any row, the last trade for the scrip in the selected row is displayed to the user underneath the table. we use the Grid_Click() event handler to display the last trade to the user. In this event handler, we first obtain the current row:
int RowNo= Grid.CurrentRowIndex;
The program obtains the StockSymbol from column 0:
string StockSymbol = Grid[RowNo,0].ToString();
The application then reads the record from the database for the selected symbol and displays it in the label field as discussed in the Transaction Form section:
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText ="select LastTradeQty,LastTradePrice,TypeOfTransaction from InvoiceTransaction where StockSymbol=’" + StockSymbol +"’";
SqlCeDataReader rd = cmd.ExecuteReader();
Next, we will look at the code that obtains the quotes from NYSE using the external web service we mentioned at the beginning of this article. This is done in the event handler of the Get Quotes button.
for(int no = 0;no<myTable.Rows.Count;no++)
{ // Taking the scrip name string StockSymbol= Grid[no,0].ToString(); // creating instance of GetCurrentPrice class new GetCurrentPrice(this,StockSymbol,myTable.Rows.Count);
}
The program iterates through each row in the data table and creates an instance of the thread class GetCurrentPrice . We will look at the implementation of this class shortly. Once all the prices are obtained the Display Quotes button in enabled. The thread class updates the database with the latest information just retrieved. Clicking on the Display Quotes button displays the current stock prices in the data table by reading the prices from the database. While displaying the price of each stock, the program also computes the net value for each stock and takes a cumulative total of all such values to compute the net worth of the entire portfolio. The net worth is displayed to the user below the data table.
Obtaining Stock Prices from NYSE
As seen above, the Net Worth screen creates an instance of GetCurrentPrice class for each record in the data table. The class constructor takes three arguments, the first argument is a reference to the NetWorth screen. This reference is used for enabling and disabling the buttons on the Net Worth form . The second parameter specifies the stock symbol for which the stock quote is to be obtained. Lastly the third parameter provides the number of records for which the stock quotes are desired.
public GetCurrentPrice(NetWorth NetWorthForm, string StockSymbol, int ScripCount)
After storing these parameters in class variables, the constructor creates a Thread and runs it. By creating threads, the application fetches several stock prices concurrently thereby improving the response time to the user.
Thread t=new Thread(new ThreadStart(Run));
t.Start();
In the Run() method, we instantiate a netxmethodsservicesstockquoteStockQuoteService class and call its getQuote() method by passing the desired stock symbol as parameter to it.
//Instantiating the webservice
netxmethodsservicesstockquoteStockQuoteService webService = new netxmethodsservicesstockquoteStockQuoteService();
//taking the currentprice by getQuote Method
Single priceValue= webService.getQuote(StockSymbol);
This invokes the remote web service hosted by http://services.xmethods.net. The getQuote() method returns the latest stock quote (delayed) for the scrip specified as parameter to the method. The method returns -1 in case of an invalid scrip code or any other error. The program displays an appropriate message to the user in such a case. If the method returns successfully, the latest quote is saved to the database by constructing an appropriate SqlCeCommand object and executing it against the database.
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "Update InvoiceTransaction set CurrentPrice=" + CurrentPrice +" where StockSymbol=’" + StockSymbol +"’";
cmd.ExecuteNonQuery();
The Run() method now updates the shared counter by locking the NetWorthForm :
lock (this.NetWorthForm)
{ NetWorthForm.counter = NetWorthForm.counter +1;
Note: The wait/notify model is not supported by Compact Framework. Thus we had to update the database in a synchronized block of code.
If all threads run to completion as indicated by the counter variable, the appropriate buttons are enabled on the Net Worth form.
if (NetWorthForm.counter == ScripCount)
{ NetWorthForm.btnDisplay.Enabled = true; NetWorthForm.btnGetQuotes.Enabled = true; NetWorthForm.btnClose.Enabled = true;
}
Clicking Display Quotes button on the Net Worth form will now display the latest quotes to the user.
In a desktop .NET application, you could have used the wait/notify model in the thread programming. In the wait/notify model, the main application (thread creator) creates a thread and waits for the notification whenever the thread completes its assigned task. The main application is free to do other work in the meanwhile. However, the compact framework does not support the wait/notify model. This has probably not implemented considering the restrictions on the memory available in mobile devices. To overcome the limitation of not having wait/notify model, we decided to update the database table after a thread runs to completion and later on read the data from the table to display it to the user.
Deleting Scrip Menu option
As mentioned previously, the user may need to clean up the database periodically to conserve resources. As memory resources are always scarce on a mobile device, it is a good practice to clean up the database periodically so as to cooperate with other applications running on the device. Over a period of use, the database may contain entries with zero stock quantity or during testing, there may be few erroneous entries stored in the database. Clicking on the Delete Record menu option opens the screen as shown in Figure 6.
Figure 6. The Delete Record Screen
The delete screen displays all the records in the database. For each record, the stock symbol and balance quantity are displayed.
Like in the Transaction Entry screen, we create and populate a DataTable in the form’s Load event handler.
//Creating the DataTable
myTable = new DataTable("InvoiceTransaction");
myTable.Columns.Add("SCRIP");
myTable.Columns.Add("QTY");
DataRow myRow = myTable.NewRow();
myTable.Rows.Add(myRow);
The FillGrid() function is then called to add the records to the grid:
//selecting the database entries to display in datagrid
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "select StockSymbol, Quantity from InvoiceTransaction";
SqlCeDataReader rd = cmd.ExecuteReader();
while(rd.Read())
{ DataRow drow = myTable.NewRow(); drow["SCRIP"]=rd.GetValue(0).ToString(); drow["QTY"]=rd.GetInt32(1); myTable.Rows.Add(drow);
}
The Grid_Click() event handler is then used to capture the scrip the user wishes to delete. In this event handler the current row is first obtained:
RowNo = Grid.CurrentRowIndex;
Afterselecting the row, the user clicks on the Delete button to delete the scrip. In the Delete_Click() handler a delete query statement is generated and executed on the database.
cmd.CommandText = "Delete from InvoiceTransaction where
StockSymbol =’"+ StockSymbol+"’";
cmd.ExecuteNonQuery();
After deleting a record, the grid is again populated using the FillGrid() function. The Close Button closes the Delete Form .
Further Work
The current application does not capture brokerage and any government-levied taxes. The brokerage can eat up substantially into your profits if you are a day-trader or a short-term trader. The application can easily be modified to capture brokerage information. Usually, the government-levied taxes constitute an extremely small factor in the entire investment and may be neglected while evaluating profits.
Further enhancements may be done by auto-scheduling the retrieval of stock prices on a daily basis or at the periodic intervals as decided by the user. This would require the use of timer services. Another useful enhancement would be to generate audible alerts to the users when a stock price crosses a predetermined threshold.
Conclusion
This case study presented the development of user-friendly personal portfolio manager software for a mobile device. The mobile application development poses several restrictions due to limited resources available on mobile devices and its limited capabilities. These have been addressed in the database design and thread programming model used by the application. These could be useful guidelines for any developer who is coding a mobile application. The application itself would be useful to any stock investor in managing her personal portfolio.
Notice: Readers are cautioned not to use this application to make actual trading decisions.
Acknowledgements
We will like to thank Ms. Nalini Yadav, Ms. Kanchan Waikar and Ms. Priti Patil for their valuable contribution in testing the application and providing valuable suggestions in improving the application functionality.
