[PC Genie]

I'm using a one line script for Yahoo finance that has little documentation in an MS Access database that is from the website Downloading Yahoo Stock Quotes. The script is as follows:

http://table.finance.yahoo.com/table.csv?s=YHOO&a=01&b=01&c=2007&d=08&e=05&f=2008&g=d&ignore=.csv

This website explains some of the parameters that are being used as:

“s=YHOO” means to download the stock prices of Yahoo. YHOO is the stock symbol of Yahoo.
“a=01&b=01&c=2007” specifies the start date in Month, Day, Year. You might have noticed that the month is subtracted with 1, which is the format required by Yahoo.
“d=08&e=05&f=2008” specifies the end date in Month, Day, Year. You might have noticed that the month is subtracted with 1, which is the format required by Yahoo.

However, these parameters are not consistant and not interchangeable with the parameters defined on the Dirk Eddelbuettel Finance or the Yahoo Data Download websites.

My goal is to be able to download all available stock quote information for a stock symbol for a selected date range. The script shown above allows me to select a date range of data, but not all the stock information. The script from the other websites allows me to download all stock information, but with no control over the date range. The stock information that I need to download includes: Stock Name, Exchange, % Change, etc. There's about a dozen of these additional information items that are available using the formatting parameter f=sl1d1t1c1ohgv, if I can find out how to get this code to work. When I try to intermingle the date range code and the data downloading format code, I get errors.

Most of my programming experience is in visual basic (VBA) for MS Access and I've searched the internet for documentation to handle this issue. If I need to post the VBA code, I can do that too. These reference websites give more information, but not enough to solve this issue. Your assistance is appreciated.

REFERENCES:
Dirk Eddelbuettel Finance
http://dirk.eddelbue...yahooquote.html
Downloading Yahoo Stock Quotes
http://www.spreadshe...ockquotes.shtml
Yahoo Data Download
http://www.gummy-stu.../Yahoo-data.htm

[Advertisements]

This is not easy. I've located Yahoo's Query Language and it looks similar to visual basic, but its entirely new to me. I still need help with this.

Yahoo! Query Language
http://developer.yahoo.com/yql/
Example
http://www.yqlblog.n...en-data-tables/
Yahoo! Developer Network
http://developer.yahoo.com/

This is my VBA module that I've been using in MS Access. It creates a temporary csv file, imports the data into a table, then deletes the cvs file. The table is also updated by deleting old data then importing new data.

Option Compare Database
Option Explicit
' **********************************************************************
Public Function BuildURL(Symbol As String, StartDate As String, EndDate As String)
	Dim DownloadURL As String
	Dim StartMonth, StartDay, StartYear As String
	Dim EndMonth, EndDay, EndYear As String
	
	StartMonth = Format(Month(StartDate) - 1, "00")
	StartDay = Format(Day(StartDate), "00")
	StartYear = Format(Year(StartDate), "00")
	  
	EndMonth = Format(Month(EndDate) - 1, "00")
	EndDay = Format(Day(EndDate), "00")
	EndYear = Format(Year(EndDate), "00")
	
	DownloadURL = "http://ichart.finance.yahoo.com/table.csv?" & _
					"s=" & Symbol & _
					"&a=" & StartMonth & _
					"&b=" & StartDay & _
					"&c=" & StartYear & _
					"&d=" & EndMonth & _
					"&e=" & EndDay & _
					"&f=" & EndYear & _
					"&g=d&ignore=.csv"
	Debug.Print DownloadURL
	
	Call GetQuotes(DownloadURL, "tblYahooData")
	
End Function
' **********************************************************************
Public Function QuoteURL(Symbol As String)
	Dim DownloadURL As String
	Dim StartDate, EndDate As String
	Dim StartMonth, StartDay, StartYear As String
	Dim EndMonth, EndDay, EndYear As String
	
	StartDate = Now()
	EndDate = Now()
	
	StartMonth = Format(Month(StartDate) - 1, "00")
	StartDay = Format(Day(StartDate), "00")
	StartYear = Format(Year(StartDate), "00")
	  
	EndMonth = Format(Month(EndDate) - 1, "00")
	EndDay = Format(Day(EndDate), "00")
	EndYear = Format(Year(EndDate), "00")
	
	DownloadURL = "http://ichart.finance.yahoo.com/table.csv?" & _
					"s=" & Symbol & _
					"&a=" & StartMonth & _
					"&b=" & StartDay & _
					"&c=" & StartYear & _
					"&d=" & EndMonth & _
					"&e=" & EndDay & _
					"&f=" & EndYear & _
					"&g=d&ignore=.csv"
	Debug.Print DownloadURL
					
	 Call GetQuotes(DownloadURL, "tblYahooData")
	
End Function
' **********************************************************************
Public Function GetQuotes(ByVal sHttp As String, ByVal sTable As String)
'http://www.dbforums.com/microsoft-access/1618879-how-query-download-directly-yahoo-finance.html
	Dim XMLHTTP As Object
	Dim byteData() As Byte
	Dim td As DAO.TableDef
	Dim ff As Integer
	Dim i As Integer
	Dim db As DAO.Database

	' Retrieve the file from the specified URL
	Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
	XMLHTTP.Open "GET", sHttp, False
	XMLHTTP.send
	byteData = XMLHTTP.responseBody
	Set XMLHTTP = Nothing

	' Write the data to a temporary CSV file
	ff = FreeFile
	Open Environ("Temp") & "\tmp.csv" For Binary Access Write As ff
		Put #ff, , byteData
	Close #1

' Delete the specified table if it already exists in the database
	DBEngine(0)(0).TableDefs.Delete sTable


	' Import the temporary CSV file into a new instance
	' of the specified table in the database
	DoCmd.TransferText acImportDelim, , sTable, _
		Environ("Temp") & "\tmp.csv", True

	' Delete the temporary CSV file
	Kill Environ("Temp") & "\tmp.csv"

End Function

' **********************************************************************

Any suggestions on how I can get Yahoo! Query Language (YQL) to work in this?

Thanks,
PC

Edited by PC Genie, 15 March 2010 - 02:21 PM.

[PC Genie]

This is not easy. I've located Yahoo's Query Language and it looks similar to visual basic, but its entirely new to me. I still need help with this.

Yahoo! Query Language
http://developer.yahoo.com/yql/
Example
http://www.yqlblog.n...en-data-tables/
Yahoo! Developer Network
http://developer.yahoo.com/

This is my VBA module that I've been using in MS Access. It creates a temporary csv file, imports the data into a table, then deletes the cvs file. The table is also updated by deleting old data then importing new data.

Option Compare Database
Option Explicit
' **********************************************************************
Public Function BuildURL(Symbol As String, StartDate As String, EndDate As String)
	Dim DownloadURL As String
	Dim StartMonth, StartDay, StartYear As String
	Dim EndMonth, EndDay, EndYear As String
	
	StartMonth = Format(Month(StartDate) - 1, "00")
	StartDay = Format(Day(StartDate), "00")
	StartYear = Format(Year(StartDate), "00")
	  
	EndMonth = Format(Month(EndDate) - 1, "00")
	EndDay = Format(Day(EndDate), "00")
	EndYear = Format(Year(EndDate), "00")
	
	DownloadURL = "http://ichart.finance.yahoo.com/table.csv?" & _
					"s=" & Symbol & _
					"&a=" & StartMonth & _
					"&b=" & StartDay & _
					"&c=" & StartYear & _
					"&d=" & EndMonth & _
					"&e=" & EndDay & _
					"&f=" & EndYear & _
					"&g=d&ignore=.csv"
	Debug.Print DownloadURL
	
	Call GetQuotes(DownloadURL, "tblYahooData")
	
End Function
' **********************************************************************
Public Function QuoteURL(Symbol As String)
	Dim DownloadURL As String
	Dim StartDate, EndDate As String
	Dim StartMonth, StartDay, StartYear As String
	Dim EndMonth, EndDay, EndYear As String
	
	StartDate = Now()
	EndDate = Now()
	
	StartMonth = Format(Month(StartDate) - 1, "00")
	StartDay = Format(Day(StartDate), "00")
	StartYear = Format(Year(StartDate), "00")
	  
	EndMonth = Format(Month(EndDate) - 1, "00")
	EndDay = Format(Day(EndDate), "00")
	EndYear = Format(Year(EndDate), "00")
	
	DownloadURL = "http://ichart.finance.yahoo.com/table.csv?" & _
					"s=" & Symbol & _
					"&a=" & StartMonth & _
					"&b=" & StartDay & _
					"&c=" & StartYear & _
					"&d=" & EndMonth & _
					"&e=" & EndDay & _
					"&f=" & EndYear & _
					"&g=d&ignore=.csv"
	Debug.Print DownloadURL
					
	 Call GetQuotes(DownloadURL, "tblYahooData")
	
End Function
' **********************************************************************
Public Function GetQuotes(ByVal sHttp As String, ByVal sTable As String)
'http://www.dbforums.com/microsoft-access/1618879-how-query-download-directly-yahoo-finance.html
	Dim XMLHTTP As Object
	Dim byteData() As Byte
	Dim td As DAO.TableDef
	Dim ff As Integer
	Dim i As Integer
	Dim db As DAO.Database

	' Retrieve the file from the specified URL
	Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
	XMLHTTP.Open "GET", sHttp, False
	XMLHTTP.send
	byteData = XMLHTTP.responseBody
	Set XMLHTTP = Nothing

	' Write the data to a temporary CSV file
	ff = FreeFile
	Open Environ("Temp") & "\tmp.csv" For Binary Access Write As ff
		Put #ff, , byteData
	Close #1

' Delete the specified table if it already exists in the database
	DBEngine(0)(0).TableDefs.Delete sTable


	' Import the temporary CSV file into a new instance
	' of the specified table in the database
	DoCmd.TransferText acImportDelim, , sTable, _
		Environ("Temp") & "\tmp.csv", True

	' Delete the temporary CSV file
	Kill Environ("Temp") & "\tmp.csv"

End Function

' **********************************************************************

Any suggestions on how I can get Yahoo! Query Language (YQL) to work in this?

Thanks,
PC

Edited by PC Genie, 15 March 2010 - 02:21 PM.

[PC Genie]

I've made two public functions to test: One created an empty table and the other is to insert data into the table from Yahoo. I still need someone to help me with this.

Option Compare Database
Option Explicit

	'Parameters for queries
	Dim strSelect As String, strFrom As String
	Dim strJoin As String, strWhere As String
	Dim strOrderBy As String, strSQL As String
	'Parameters for recordset
	Dim db As DAO.Database, tblDef As DAO.TableDef
	Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset, rst3 As DAO.Recordset
	Dim intRecCount1 As Integer, intRecCount2 As Integer
	Dim i As Integer, j As Integer
'   Flat file coversion

Public Function MakeQuoteTable()  ' Make a new temporary table "tmpYahooDownload"
On Error GoTo Whoops
	Dim sTable As String
	sTable = "tmpYahooDownload"
	DBEngine(0)(0).TableDefs.Delete sTable

	Dim db As Database
	Dim tblDef As TableDef
	Set db = CurrentDb()
	Set tblDef = db.CreateTableDef(sTable)
		
		With tblDef
		
'Ask,AverageDailyVolume,Bid,AskRealtime,BidRealtime,BookValue,Change&PercentChange,Change,Commission,
			.Fields.Append .CreateField("Ask", dbText)
			.Fields.Append .CreateField("AverageDailyVolume", dbText)
			.Fields.Append .CreateField("Bid", dbText)
			.Fields.Append .CreateField("AskRealtime", dbText)
			.Fields.Append .CreateField("BidRealtime", dbText)
			.Fields.Append .CreateField("BookValue", dbText)
			.Fields.Append .CreateField("Change&PercentChange", dbText)
			.Fields.Append .CreateField("Change", dbText)
			.Fields.Append .CreateField("Commission", dbText)
		End With
	db.TableDefs.Append tblDef
	
	Call InsertQuoteData
	
OffRamp:
	Exit Function
Whoops:
	MsgBox "Error #" & Err & ": " & Err.Description
	Resume OffRamp

End Function

' ==================================================================

Public Function InsertQuoteData()  ' Insert data into "sTable"
	Dim DataURL As String
	Dim db As Database
	Dim sTable As String

	Dim strSymbol As String, QuoteSource As String
	Dim frmCurrentForm As Form
	Set frmCurrentForm = Screen.ActiveForm
	
	sTable = "tmpYahooDownload"
	strSymbol = frmCurrentForm.Symbol
	
	DataURL = "http://download.finance.yahoo.com/d/quotes.csv?" & _
	"f=aa2bb2b3b4cc1c3" & _
	"&s=" & strSymbol
	
	QuoteSource = "http://ichart.finance.yahoo.com/table.csv?"
		'Insert data into temporary table.
			Set db = CurrentDb()

			Set rst1 = db.OpenRecordset("QuoteSource", dbOpenDynaset) 'Target
			Set rst2 = db.OpenRecordset("sTable")  'Source
			   
		rst2.MoveFirst
		Do Until rst2.EOF
				
			With rst1
				.AddNew
				
'Ask,AverageDailyVolume,Bid,AskRealtime,BidRealtime,BookValue,Change&PercentChange,Change,Commission,
				![Ask] = rst2!a
				![AverageDailyVolume] = rst2!a2
				![Bid] = rst2!b
				![AskRealtime] = rst2!b2
				![BidRealtime] = rst2!b3
				![BookValue] = rst2!b4
				![Change&PercentChange] = rst2!c
				![Change] = rst2!c1
				![Commission] = rst2!c3
		   
				
				.Update
			End With
		
		rst2.MoveNext
		Loop
		
				rst1.MoveLast
				rst1.MoveFirst
				intRecCount1 = rst1.RecordCount
				Debug.Print "intRecCount1 = " & intRecCount1
				rst2.MoveLast
				rst2.MoveFirst
				intRecCount2 = rst2.RecordCount
				Debug.Print "intRecCount2 = " & intRecCount2
		
		rst2.Close
		Set rst2 = Nothing
		rst1.Close
		Set rst1 = Nothing
		db.Close
		Set db = Nothing
				
End Function

Edited by PC Genie, 16 March 2010 - 06:45 PM.