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 sheetNocr 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



4 comments:

Bhavin Shah said...

a good handy one madhur-bhai...keep it up.

Anonymous said...

Very useful,

Thanks, I was looking for just this one

Woundering if we can import a chart, or create one with a set of quotes,

also check this one out

http://getquote-tedsoft.blogspot.com/p/about.html

thanks once again

Madhur Kumar Tanwani said...

Nopes - it is just a simple tool to track the sayings :)

The getquote extension seems promising.

Anonymous said...

Thank you very much for the basic code, but for multiple stocks, it was rather slow. I hacked your code to get multiple quotes at once.

thanks again, /alan

Sub FastFillStockQuotes
Dim c as Integer, r as Integer, destCol as Integer, sheetNo as Integer, yieldCol as Integer
Dim Sheet as Object
Dim symbol as String
Dim stockQuote as Currency
Dim stocks as String

REM Which sheet contains the above rows and columns. This is a zero based index (First sheet is sheet no 0)
sheetNo=0
REM Which column has the stock symbol. This is a zero based index (1st column is column no 0)
c=0
REM Which row has the stock 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=9
yieldCol = 14

stocks = ""

REM print "starting fastfillstockquotes"
Sheet = thisComponent.Sheets(sheetNo)
do while true
symbol = Sheet.getCellByPosition(c, r).String
REM print "stock", symbol
if symbol = "" then
REM Print "Collection Complete"
exit do
end if
symbol = "+" + symbol + "+"
REM print "stock2", symbol
if InStr(stocks,symbol) = 0 then
stocks = stocks + Right(symbol,Len(symbol)-1)
end if
r=r+1
loop

stocks = Left(stocks,Len(stocks)-1)
REM print "stocks ", stocks
GetStockQuotes(stocks, Sheet, c, destCol, yieldCol)
thisComponent.Sheets.removeByName("Link")
Print "Fill Stocks Complete"
End Sub

Function GetStockQuotes(symbol as String, dSheet as Object, sourceCol as Integer, dCol as Integer, yCol as Integer)

REM print "GetStockQuotes"

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, yValue As Double
Dim oSheet as Object, oSheets as Object
Dim rS as Integer, rD as Integer
sUrl = "http://in.finance.yahoo.com/d/quotes.csv?s=" & symbol & "&f=sl1y"
REM print "sURL", sUrl
sFilter = "Text - txt - csv (StarCalc)"
REM print "sFilter", sFilter
sOptions = "44,34,SYSTEM,1,1/10/2/10/3/10/4/10/5/10/6/10/7/10/8/10/9/10"
REM print "sOptions", sOptions

oSheet = createSheet(thisComponent.Sheets)
REM print "createSheet"
oSheet.LinkMode = com.sun.star.sheet.SheetLinkMode.NONE
REM print "linkMode"
oSheet.link(sUrl, "", sFilter, sOptions, 1 )
REM print "link"

REM for each stock return from finance.yahoo, match against the
REM soruce sheet and fill in current price and yield
rS = 0
do while true
stockSymbol = oSheet.getCellByPosition(0,rS).String
if stockSymbol = "" then
exit do
end if
REM print "stockSymbol", stockSymbol
fValue = oSheet.getCellByPosition(1,rS).Value
REM print "fValue", fValue
yValue = oSheet.getCellByPosition(2,rS).Value
rD = 2
do while true
symbol = dSheet.getCellByPosition(sourceCol, rD).String
REM print "stock", symbol
if symbol = "" then
exit do
end if
if symbol = stockSymbol then
dSheet.getCellByPosition(dCol, rD).Value = fValue
dSheet.getCellByPosition(yCol, rD).Value = yValue
end if
rD = rD + 1
loop
rS = rS + 1
loop
End Function

 
Stats