BASIC Macro To Get Stock Quotes for OpenOffice Calc

I've been trying to figure out to keep track of various tips, suggestions and recommendations that various financial experts throw all the time on all the major stock market information websites. While I definitely like moneycontrol.com (for India markets), I really hate the fact that it does not allow me to track my watch list conveniently. For example, I add stock to a watch list, I enter some notes, but then those notes are not visible to me while viewing the watch list :(

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 :

1. The script was written for OpenOffice Calc, but should work in Excel as well.
2. 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.
3. 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.
4. 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.

Here is the Macro :

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