In continuation with last post on fetching NSE live share prices into Excel, we will try to add few more features to this Excel workbook (NSE BSE Live Stock Prices in Excel) and develop into a portfolio management product.
Main features of the product –
- Live Share price from NSE (low delay compared to other public websites)
- NSE Ticker lookup for all the NSE listed companies (no query to exchange)
- Track the stock portfolio with return % and no sign in (user tracking not required)
- Excel power of calculating and formatting (computational power and dashboard visualization)
Scope for future features of the product –
- Triggering alert on specified price levels of stock (popup message / email alert)
- Moving averages and price charts for a selected stock (technical analysis)
- Fundamental analysis based on the filings (XBRL will make it easy)
- Research reports linking and public information for a selected stock
As discussed in last post, the data source for stock information is crucial for the entire product functionality. Here, the selected source of NSE website is of low delay (~2 mins) compared to other finance / money related websites (~15min). Given the data is fetched through the NSE ticker symbol which needs to be specified in the product, the users may find difficult to get the exact NSE symbol so we have incorporated the symbol look up functionality. Instead of querying the exchange for every lookup search, we will keep the local data source. This will reduce the web traffic and improve the user experience in displaying the search results without any lag. The source data here is taken from NSE website of all the listed companies as on 30th Nov 2014. So when the user queries any part of the company name, all the related companies along with its ticker symbol are displayed so that user can select and click to add to the existing portfolio. To compute the mark to market returns (in terms of percentage) the user needs to enter the purchase price and may include the transaction costs (brokerage & other charges) as part of their purchase price. To get the absolute returns, the user can enter the purchase quantity to get the absolute return and profit (before exit taxes) of the portfolio. Excel has powerful functionality of formatting apart from the strong computing power. The mark to market returns and each share weightage of portfolio are formatted with basic conditional formatting. Given the product is in early stages of its functionality enhancement, this blog post is not elaborate on the detailed Excel formatting of the product. The entire formatting (static / conditional) along with dashboard creations will itself a product enhancement which will be addressed in later posts.
Update All – One of my friend (who is not Excel expert but expert of another technology) is using the workbook and mentioned to me that calculate now (pressing F9) is not updating the latest prices. This is mainly because of VBA UDF refresh bug which do not recalculates all the UDF function on sheet calculation. One way to solve this problem is use the Ctrl + Alt + F9 key strokes to make the full calculations of the workbook or use Ctrl + Alt + Shift + F9 key combination to make the full calculation rebuild of the workbook. Instead of making the user to remember and press these keys, we can write one line macro code and link it with a button. Following is the VBA code of these functionality –
Ctrl + Alt + F9 : Application.CalculateFull Ctrl + Alt + Shift + F9 : Application.CalculateFullRebuild
Here we are using only calculate full code and link to the button named as “Calculate All” NSE Symbol Lookup – The other functionality enhancement of NSE symbol lookup is done by the Excel Auto Suggest code as explained in the previous post, link. The related graphical user interface (GUI) is created through VBA userforms. When the user enters more than 3 characters in the inputbox, the list boxes updated with all company names with search string and also corresponding NSE ticker symbols. Once the user selects the desired companies / tickers and click on the “Add Selected Tickers” on the form, all the selected companies are added to the portfolio. Following is the user action flow diagram of this functionality – Basic conditional formatting is applied on portfolio weight of the each stock and on returns. The product file of Excel portfolio management can be downloaded from this link, NSE Live Stock Prices in Excel Ver 2 – Portfolio Mangement Through Excel. <not working>
Please refer to the latest post for the updated version – NSE Live Stock (Share Price) Data into Excel – Part 3