Results 1 to 10 of 10

Sample database retrieving Yahoo Finance info with WinHTTP into an Access table

  1. #1
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306

    Sample database retrieving Yahoo Finance info with WinHTTP into an Access table


    The attached zip file contains an Word document (YahooFinDemoInfo.docx) describing the enclosed MsAccess 2010 database (DemoYahFin.accdb).

    It is important to read the YahooFinDemoInfo material to understand what is included and how the demo works. This demo uses WinHTTP and retrieves info into an Access table. This is a demo with a limited set of financial data, and a limited set of Stock/Ticker symbols.

    This demo was initiated by thread Market Data Retrieval

    The related vba code has numerous comments throughout.
    Attached Files Attached Files
    Last edited by orange; 04-12-2016 at 08:35 PM. Reason: additional info/spelling

  2. #2
    tdawg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    2
    Hi Orange,
    I am new to this forum and VBA but not new to Access. I tried to run frmYahooFinance and the standalone demo but got the same error both times. The error is "Response from Yahoo is 301" and the code gives me the error: Click image for larger version. 

Name:	301-error-in-Yahoo-retrieval.JPG 
Views:	108 
Size:	19.0 KB 
ID:	28509.

    Could this be caused by the fact that my Access 2010 is 64bit?
    I like your suggestion about adding a table of favorite tickers that the stand-alone proc could use. But I need to get this running first.
    TDawg

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    ?? I don't think it has anything to do with 64 bit.
    I just tried with debugging turned on and received a 301 from Yahoo.
    Here are my debugging statements:

    Code:
    ===Starting New Session  Ticker=====================================
    
    Ticker symbols selected is/are AAPL+BA+CAT
     bTickersAcceptable  True
    -Requested fieldnames Symbol,StockName,LastTradePrice
    -Requested flddataTypes Varchar(10),Varchar(50),Currency
    Parm string matching these fields for Yahoo <snl1>
    Going to  procedure StatusTblYahooFin to process fields and local table info
    Entering StatusTblYahooFin  with 
     -Fields   -Symbol,StockName,LastTradePrice
     -Datatypes -Varchar(10),Varchar(50),Currency
    Current Table fields count is 4
     Requested field count is 3
    Field count in existing table does not match fieldcount in current request
     so must create  tblYahooFin with latest parms/fields
     drop the table then
     call the create table routine with latest fields
    tblYahooFin deleted 
    Entering createFinTable 
    finalsql-- create Table tblYahooFin( Symbol  Varchar(10) , StockName  Varchar(50) , LastTradePrice  Currency )
    new table created
    YahFin tbl exists
    bFieldsAcceptable True
    Set up URL and send request here
     Constructed URL to be sent to Yahoo finance http://finance.yahoo.com/d/quotes.csv?s=AAPL+BA+CAT&f=snl1
    Request sent to Yahoo Fin
    Request Status from Yahoo is 301
    Request response received from Yahoo Fin
    Processing data from Yahoo Fin for insertion into local table
    
    <HTML>
    fld 0  Symbol  <HTML>
    
    ---record(0) from Yahoo Fin inserted into local table---
    
    <HEAD>
    fld 0  Symbol  <HEAD>
    
    ---record(1) from Yahoo Fin inserted into local table---
    
    <TITLE>Document Has Moved</TITLE>

    Seems like Yahoo may have moved/changed the link.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    Just did some additional checking.
    The URL for the stock quotes was changed.

    I changed line 90 in btnGetYahooInfo click event to this
    Code:
    90        URLtoSend = "http://download.finance.yahoo.com/d/quotes.csv?s=" & Me.tbxTickSymbols & "&f=" & Me.mySelections
    and now it works fine.

    Yahoo had modified the url to include the download preface.
    Code:
    LastTradeDate Symbol StockName LastTradePrice
    5/3/2017 AXP American Express Company $78.83
    5/3/2017 AAPL Apple Inc. $147.06
    5/3/2017 BA Boeing Company (The) $183.39
    5/3/2017 NKE Nike, Inc. $54.53
    5/3/2017 AXP American Express Company Common $78.83
    5/3/2017 AAPL Apple Inc. $147.06
    5/3/2017 CAT Caterpillar, Inc. Common Stock $101.54
    5/3/2017 MSFT Microsoft Corporation $69.08
    I did not update the code nor replace the zip file in Post #1.
    You'll have to modify the code, but it should work.

    Here is the debugging dialog that was produced.
    Code:
    === Starting New Session  frmOpen====
    ===Starting New Session  Ticker=====================================
    
    Ticker symbols selected is/are AXP+AAPL+BA+NKE
     bTickersAcceptable  True
    -Requested fieldnames LastTradeDate,Symbol,StockName,LastTradePrice
    -Requested flddataTypes Varchar(10),Varchar(10),Varchar(50),Currency
    Parm string matching these fields for Yahoo <d1snl1>
    Going to  procedure StatusTblYahooFin to process fields and local table info
    Entering StatusTblYahooFin  with 
     -Fields   -LastTradeDate,Symbol,StockName,LastTradePrice
     -Datatypes -Varchar(10),Varchar(10),Varchar(50),Currency
    existing field LastTradeDate requested field LastTradeDate >>same
    existing field Symbol requested field Symbol >>same
    existing field StockName requested field StockName >>same
    existing field LastTradePrice requested field LastTradePrice >>same
    User Selected ... Yes = DELETE
    tblYahooFin will be deleted
     and new tblYahooFin with latest parms/fields created
    Drop existing tblFin 
    tblYahooFin deleted 
    Entering createFinTable 
    finalsql-- create Table tblYahooFin( LastTradeDate  Varchar(10) , Symbol  Varchar(10) , StockName  Varchar(50) , LastTradePrice  Currency )
    new table created
    YahFin tbl exists
    bFieldsAcceptable True
    Set up URL and send request here
     Constructed URL to be sent to Yahoo finance http://download.finance.yahoo.com/d/quotes.csv?s=AXP+AAPL+BA+NKE&f=d1snl1
    Request sent to Yahoo Fin
    Request Status from Yahoo is 200
    Request response received from Yahoo Fin
    Processing data from Yahoo Fin for insertion into local table
    
    "5/3/2017","AXP","American Express Company",78.83
    fld 0  LastTradeDate  5/3/2017
    fld 1  Symbol  AXP
    fld 2  StockName  American Express Company
    fld 3  LastTradePrice  78.83
    
    ---record(0) from Yahoo Fin inserted into local table---
    
    "5/3/2017","AAPL","Apple Inc.",147.06
    fld 0  LastTradeDate  5/3/2017
    fld 1  Symbol  AAPL
    fld 2  StockName  Apple Inc.
    fld 3  LastTradePrice  147.06
    
    ---record(1) from Yahoo Fin inserted into local table---
    
    "5/3/2017","BA","Boeing Company (The)",183.39
    fld 0  LastTradeDate  5/3/2017
    fld 1  Symbol  BA
    fld 2  StockName  Boeing Company (The)
    fld 3  LastTradePrice  183.39
    
    ---record(2) from Yahoo Fin inserted into local table---
    
    "5/3/2017","NKE","Nike, Inc.",54.53
    This field value contains a comma?? "5/3/2017","NKE","Nike, Inc.",54.53
    fld 0  LastTradeDate  5/3/2017
    fld 1  Symbol  NKE
    fld 2  StockName  Nike, Inc.
    fld 3  LastTradePrice  54.53
    
    ---record(3) from Yahoo Fin inserted into local table---
    
    03-May-2017 5:42:11 PM -Finished Processing  data fom Yahoo Finance into local table ******** 
    
    Rowsource of  fields/parms listbox is 
    SELECT [YparmInfo].[ID], [YparmInfo].[YParm], [YparmInfo].[YMeaning], [YparmInfo].[YFormat] FROM YparmInfo ORDER BY [ID]; 
    
    === Starting New Session  frmOpen====
    ===Starting New Session  Ticker=====================================
    
    Ticker symbols selected is/are AXP+AAPL+CAT+MSFT
     bTickersAcceptable  True
    -Requested fieldnames LastTradeDate,Symbol,StockName,LastTradePrice
    -Requested flddataTypes Varchar(10),Varchar(10),Varchar(50),Currency
    Parm string matching these fields for Yahoo <d1snl1>
    Going to  procedure StatusTblYahooFin to process fields and local table info
    Entering StatusTblYahooFin  with 
     -Fields   -LastTradeDate,Symbol,StockName,LastTradePrice
     -Datatypes -Varchar(10),Varchar(10),Varchar(50),Currency
    existing field LastTradeDate requested field LastTradeDate >>same
    existing field Symbol requested field Symbol >>same
    existing field StockName requested field StockName >>same
    existing field LastTradePrice requested field LastTradePrice >>same
    User Selected ... Yes = DELETE
    tblYahooFin will be deleted
     and new tblYahooFin with latest parms/fields created
    Drop existing tblFin 
    Entering createFinTable 
    finalsql-- create Table tblYahooFin( LastTradeDate  Varchar(10) , Symbol  Varchar(10) , StockName  Varchar(50) , LastTradePrice  Currency )
    YahFin tbl exists
    bFieldsAcceptable True
    Set up URL and send request here
     Constructed URL to be sent to Yahoo finance http://download.finance.yahoo.com/d/quotes.csv?s=AXP+AAPL+CAT+MSFT&f=d1snl1
    Request sent to Yahoo Fin
    Request Status from Yahoo is 200
    Request response received from Yahoo Fin
    Processing data from Yahoo Fin for insertion into local table
    
    "5/3/2017","AXP","American Express Company Common",78.83
    fld 0  LastTradeDate  5/3/2017
    fld 1  Symbol  AXP
    fld 2  StockName  American Express Company Common
    fld 3  LastTradePrice  78.83
    
    ---record(0) from Yahoo Fin inserted into local table---
    
    "5/3/2017","AAPL","Apple Inc.",147.06
    fld 0  LastTradeDate  5/3/2017
    fld 1  Symbol  AAPL
    fld 2  StockName  Apple Inc.
    fld 3  LastTradePrice  147.06
    
    ---record(1) from Yahoo Fin inserted into local table---
    
    "5/3/2017","CAT","Caterpillar, Inc. Common Stock",101.54
    This field value contains a comma?? "5/3/2017","CAT","Caterpillar, Inc. Common Stock",101.54
    fld 0  LastTradeDate  5/3/2017
    fld 1  Symbol  CAT
    fld 2  StockName  Caterpillar, Inc. Common Stock
    fld 3  LastTradePrice  101.54
    
    ---record(2) from Yahoo Fin inserted into local table---
    
    "5/3/2017","MSFT","Microsoft Corporation",69.08
    fld 0  LastTradeDate  5/3/2017
    fld 1  Symbol  MSFT
    fld 2  StockName  Microsoft Corporation
    fld 3  LastTradePrice  69.08
    
    ---record(3) from Yahoo Fin inserted into local table---
    
    03-May-2017 5:47:26 PM -Finished Processing  data fom Yahoo Finance into local table ********
    Good luck.

  5. #5
    tdawg is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    2
    Hi Orange,
    The patch worked like a charm. Thanks so much.
    Regards,
    Ted

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    Update: re Yahoo Finance info

    From another forum dated Aug 1, 2018

    I wouldn't say they(YahooFinance) had a new API. I would say they unplugged their APIs for both current quotes and historical quotes. They still offer CSV files for their portfolio and historical quotes, but they require credentials ("&userid", or "&crumb" and cookie). For most purposes, their web pages are now loaded from data in JSON files, including historical quotes, option quotes, and their portfolio function.

    Another source of info re: Yahoo Finance API has been taken down
    Last edited by orange; 08-18-2018 at 11:59 AM. Reason: explain why this sample no longer works

  8. #8
    jessdaddy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    2
    Is there a new site from which we can pull currency exchange/forex data into Access? I have seen a couple of posts related to JSON, but I am not sure of how to go about doing that. Any easy step-by-step tutorials available? Looking to retrieve one or two exchange rate values only into unbound fields in my Access forms.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306

  10. #10
    jessdaddy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    2
    Quote Originally Posted by orange View Post
    Not really. I did what I did as an exercise/demo.

    You might try https://www.ecb.europa.eu/stats/poli.../index.en.html
    Okay, thanks. I will check it out.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2003 Northwind Sample Database
    By Troop in forum Access
    Replies: 2
    Last Post: 05-18-2013, 10:50 PM
  2. Replies: 4
    Last Post: 12-05-2012, 03:24 PM
  3. Replies: 6
    Last Post: 06-27-2011, 07:11 PM
  4. SQL- retrieving info
    By jmarti57 in forum Programming
    Replies: 0
    Last Post: 12-10-2008, 03:05 PM
  5. Yahoo merchant database
    By sammie in forum Forms
    Replies: 0
    Last Post: 05-09-2006, 01:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums