Also, since what I am maintaining is a watch list, I would like to record recommended target / buy prices ad trigger alerts on those. So I came up with this small spreadsheet template that will record all this information and give a snapshot of the watch list.
The biggest blocker in this task was how to populate the "current" stock price. So, I wrote this simple BASIC Macro that, given a stock symbol, will fetch the current price of the stock. Salient points about the script :
- The script was written for OpenOffice Calc, but should work in Excel as well.
- The script pulls its data from in.finance.yahoo.com, but this can be replaced with the country's specific address and everything should just work fine. For example, for US market replacing http://in.finance.yahoo.com with http://download.finance.yahoo.com will work as-is.
- You can also replace the source http://in.finance.yahoo.com, with something completely different (for example http://www.nseindia.com/marketinfo/equities/ajaxGetQuote.jsp), but then the parsing of the returned data will have to change.
- To start using the script for your spreadsheet, do ensure that the sheetNo, c, r and destCol variables at the start of the script point to correct locations in your file.
Sub FillStockQuotes Dim c as Integer, r as Integer, destCol as Integer, sheetNo as Integer Dim Sheet, Cell, DestCell Dim symbol as String REM Which sheet contais the above rows and columns. This is a zero based index (First sheet is sheet no 0) sheetNo=0 REM Which column has the styock symbol. This is a zero based index (1st column is column no 0) c=1 REM Which row has the styock symbol. This is a zero based index (3rd row is row no 2) r=2 REM Which column do you want to store the quote in. This is a zero based index destCol=6 Sheet = thisComponent.Sheets(sheetNo) do while true Cell = Sheet.getCellByPosition(c, r) symbol = Cell.String if symbol = "END OF SYMBOLS" then Print "Processing Complete" exit do end if Dim stockQuote as Currency stockQuote = GetSymbolQuote(symbol, Sheet) REM Print " " & symbol & " : " & stockQuote if stockQuote <> "" then DestCell = Sheet.getCellByPosition(destCol, r) DestCell.Value = stockQuote end if r=r+1 loop End Sub Function GetSymbolQuote(symbol as String, aSheet as Object) As Currency if symbol = "" then GetSymbolQuote = "" Exit Function end if Dim sUrl As String, sFilter As String Dim sOptions As String Dim stockSymbol As String Dim fValue As Double Dim oSheet as Object, oSheets as Object sUrl = "http://in.finance.yahoo.com/d/quotes.csv?s=" & symbol & "&f=sl1" sFilter = "Text - txt - csv (StarCalc)" sOptions = "44,34,SYSTEM,1,1/10/2/10/3/10/4/10/5/10/6/10/7/10/8/10/9/10" oSheet = createSheet(thisComponent.Sheets) oSheet.LinkMode = com.sun.star.sheet.SheetLinkMode.NONE oSheet.link(sUrl, "", sFilter, sOptions, 1 ) stockSymbol = oSheet.getCellByPosition(0,0).String fValue = oSheet.getCellByPosition(1,0).Value GetSymbolQuote = fValue End Function Function createSheet(oSheets as Object) Dim oSheet as Object If oSheets.hasByName("Link") Then oSheet = oSheets.getByName("Link") Else oSheet = oDocument.createInstance("com.sun.star.sheet.Spreadsheet") oSheets.insertByName("Link", oSheet) oSheet.IsVisible = False End If createSheet = oSheet End Function