Convert Excel vba to Access

[TEB2]

Background: I have to evaluate over 500 stocks on a monthly basis to
determine the market-to-cost adjustment. Part of the process is to compare
the current market price to the previous six month high.

The following 2 subs go to Yahoo Finance and pulls 6 pieces of information:
symbol, date, open, high, low, close based on the date range I enter.
However, it is limited by the 256 columns in Excel. Therefore, I can only
download 49 stocks at a time. I need help converting this code to Access so
I can download all 500 stock's data into a table. Also, I want to maintain
the stock symbols to look up in a seperate table called "Stocks".

Sub GetStockData()

Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim nQuery As Name

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet

StartDate = DataSheet.Range("B2").Value
EndDate = DataSheet.Range("B3").Value
Symbol = DataSheet.Range("B4").Value
Range("C7").CurrentRegion.ClearContents

'URL for the query
qurl = "http://chart.yahoo.com/table.csv?s=" & Symbol
qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate)
& _
"&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("G3") &
"&q=q&y=0&z=" & _
Symbol & "&x=.csv"
Range("b5") = qurl

QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.saveData = True
End With

Range("C7").CurrentRegion.TextToColumns
Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False

Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm
d/yy"
Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00"
Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000"
Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00"


With ThisWorkbook
For Each nQuery In Names
If IsNumeric(Right(nQuery.Name, 1)) Then
nQuery.Delete
End If
Next nQuery
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Range("C7:I2000").Select
Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C1").Select
Selection.ColumnWidth = 12

Range("B4").Select

End Sub
Sub SaveStockData()

Dim n As Integer, i As Integer

n = Range("L4")

clear

For i = 1 To n

Range("B4") = Cells(7 + i, 11)

GetData

Cells(6, 5 * i + 7) = Cells(7 + i, 11)
Range("C7:G2000").Select
Selection.Copy
Cells(7, 5 * i + 7).Select
ActiveSheet.Paste

Cells(6, 5 * i + 7).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 48
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 48
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 48
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 48
End With

Next i

Range("A1").Select

End Sub

 

[Scott Lichtenberg]

TEB,

I was able to cut and paste your url into a browser, and I got back a CSV
file with your six fields, one record per date for the selected time period.
I was only able to get Yahoo to return 1 stock at a time. I couldn't figure
out the code to list more than one symbol. I'm assuming that when you run
this, you get the same format, one row per day, only with 49 sets of columns
representing your 49 stocks.

If this is the case, and if Yahoo can handle a query with 500 symbols, you
might be able to pull the data into Access - although not without some
difficulty. Your first problem is that your recordlength is going to be
enormous. You are going to have 3000 fields, each 4-5 characters, plus a
delimeter. This is going to give you a 15,000+ character record that you
are going to have to work with. As you note, you can't open this in Excel.
Access, however, still supports bit by bit processing of a file. Use the
online help feature for VBA to look up the Open and Input # statements. You
ought to be able to read through each record looking for commas and new-line
characters.

One thing you'll want to think about is the format of the Access table that
will hold your output. You are not going to want a 3000 field record.
Rather, you should have a structure something like this:

Symbol
Date
High
Low
End

This will give you one record for each symbol for each date.


Hope this helps.

 

[TEB2]

Thanks for your response Scott!

I can list as many stocks as I want in column K starting in cell K8. By
trial and error, I found that 49 is the magic number.

The code starts at K8 and downloads the data one at a time then loops back
to K9...etc until it finds an empty cell.

So, can Access do the same for each stock in a Stock table and append each
data file to a Data table?

 

[Paul]

Date is a reserved keyword in Access your program may break while in
operation. Use something like PriceDate instead.