Pulling Stock Investment Data from Web into Access Table (1 Viewer)

[HMikeWenzel]

I know it is possible to pull stock investment data, like the current stock price, from web sources like Yahoo into Microsoft Excel. How do you do this in Microsoft Access?

I have been using Excel to organize data for my stock investments. But it is getting to be very messy to try to analyze the data in various dimensions, like by investment type, by country, etc. It seems like Access would be the perfect tool to maintain all the data, then query it in many different ways. But I would like to avoid manually updating the information like prices, dividend rates, etc.

Is there a structural reason that I need to pull the data into Excel first, then link to it from Access?

I'm new to Access, but I'm a senior programmer.

Thanks!

 

[gemma-the-husky]

it was either here of on utteraccess, that this same question was asked in the last day or two, and a full solution was identified to "scrape" Yahoo, and extract the data.

Sorry, I haven't got the link, but I am sure you can find it.

 

[PC User]

It appears that Google provides stock quotes.
How to get a real-time stock quote using Google API
http://yken.org/2009/01/05/how-to-get-a-real-time-stock-quote-using-google-api/
Google Finance APIs and Tools
http://code.google.com/apis/finance/docs/2.0/reference.html#Feeds
Historical Quotes - Google Finance
http://www.etraderzone.com/free-scripts/49-historical-quotes-google-finance.html
Google Quotes Excel Spreadsheet:
http://www.etraderzone.com/scripts/historical-google.zip

This is the VB code from the Excel Spreadsheet.

Public fname, fn, DIYSub_Dir, SD, SM, SY, SY_2
Sub Download()

Set occXMLHTTP = CreateObject("Microsoft.XMLHTTP")
Set fso = CreateObject("Scripting.FileSystemObject")
     
DIY_Dir = "c:\eTraderZone\"
DIYSub_Dir = "c:\eTraderZone\tickers\"
     
If Not fso.FolderExists(DIY_Dir) Then
   MkDir DIY_Dir
End If
     
If Not fso.FolderExists(DIYSub_Dir) Then
   MkDir DIYSub_Dir
End If
     
Check_Date
PFROW = 1

Do Until Worksheets("Portfolio").Cells(PFROW, 1) = ""
   PFROW = PFROW + 1
Loop

PFROW = PFROW - 1

For x = 1 To PFROW
   fn = Worksheets("Portfolio").Cells(x, 1)
   fname = Worksheets("Portfolio").Cells(x, 1) & ".txt"
   occXLS = DIYSub_Dir & fname
   occUrl = "http://finance.google.com/finance/historical?q=" & Trim
(Worksheets("Portfolio").Cells(x, 1)) & _
   "&startdate=" & SM & "+" & SD & "+" & SY & "&enddate=" & SM & "+" & SD &
"+" & SY_2 & "&output=csv"
   occLocalFile = DIYSub_Dir & fname
   occLocalFileName = Worksheets("Portfolio").Cells(x, 1) & ".txt"
       
   occXMLHTTP.Open "GET", occUrl, False
   occXMLHTTP.send
   occArray = occXMLHTTP.ResponseBody
   occfile = 1
           
   Open occLocalFile For Binary As #occfile
   Put #occfile, , occArray
   Close #occfile
   
   RemoveLine
Next

Response = MsgBox _
("Download Completed." & vbCrLf & _
"Open C:\eTraderZone\Tickers to view files ?", vbYesNo)

If Response = vbYes Then
   RetVal = Shell("explorer " & DIYSub_Dir, 1)
End If

End Sub

 

[Harrymon12]

For this, I had been using this and it's great.
It has instructions which can help you.
I have been using MarketXLS for this, it's been working Great so far
Just visit the site for more info.
They also have a live support and a forum to help you with your questions/concerns.

 

[jdraw]

There is a sample getting info from Yahoo Finance into Access here.

 

[Harrymon12]

I have been using MarketXLS for this, it's been working Great so far
It has instructions which can help you.
Just visit the site for more info.
They also have a live support and a forum to help you with your questions/concerns.

 

[Harrymon12]

I had used MarketXLS for this. It works for me great.