Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Grefcon901 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    16

    Market Data Retrieval

    I have this done in excel but my problem is my Access ignorance TBH. Using the Yahoo Finance API through Excel is pretty simple, but can not find anyone who is doing this within Access. If anyone has time to walk me through how this is possible I would forever be in your debt.

    Here is a link to google drive sheet with a sample of kinda how I am doing things. It looks up the symbol and what specific data set I am looking for on the Yahoo Finance API.
    Each Cell Has a formula to search the yahoo API for the specific data required.

    Download the sample to see.
    https://drive.google.com/file/d/0B_i...ew?usp=sharing

    Click image for larger version. 

Name:	Screenshot (2).png 
Views:	92 
Size:	68.7 KB 
ID:	24078Click image for larger version. 

Name:	Screenshot (3).png 
Views:	92 
Size:	81.7 KB 
ID:	24079 The second image here has a sample formula shown of how excel retrieves the data.

    Here is a link for a Yahoo API explanation and UI pieces list.



    https://greenido.wordpress.com/2009/...ce-hidden-api/


    -Jon
    Last edited by Grefcon901; 03-14-2016 at 01:27 PM.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could show readers your Excel solution. The vba, if that is the basis of your approach, may be very similar. Readers really need something re: your calling of Yahoo Finance API and subsequent manipulations.
    Use of XML/HTML/JSON??? etc.

  3. #3
    Grefcon901 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    16
    Thank you @orange , Is that a bit better? I would have taken a screen shot to upload but I figured this was easier.

    -jon

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ok.
    I used a link on the wordpress page and got this
    Click image for larger version. 

Name:	BasedOnALinkOnThePage.jpg 
Views:	93 
Size:	68.6 KB 
ID:	24077

    and then downloaded the spreadsheet (actually came to me as a csv; I don't use excel)
    Code:
    Date,Open,High,Low,Close,Volume,Adj Close
    
    2010-02-22,16.379999,16.40,16.120001,16.18,8705700,13.924164
    
    2010-02-19,16.389999,16.459999,16.27,16.35,6963200,14.070462
    I think what you are looking for is:
    -how to import an excel or csv file into Access
    -how to build an Access table
    -how to build an Access query...
    or ....

    There will be youtube videos on most of this.

  5. #5
    Grefcon901 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    16
    Quote Originally Posted by orange View Post
    Ok.
    I used a link on the wordpress page and got this
    Oh haha, I really should have done a better job explaining. That link is not for the browser but for excel formulas or VBA to gain access the hidden Yahoo API. You use those URL modules to retrieve certain data sets.

    but you think there is no way to do the same thing inside of access tables? I would just need to link it? :/

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As I said earlier in post#2, it would be useful to readers to see your excel solution.
    How are you getting the data now--automated??

    What is it about Access that you want to take advantage of?
    If you can get the file into excel, you can probably link to it from Access for queries/reports.

  7. #7
    Grefcon901 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    16
    does that make more sense @orange ? sorry for the issues.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not an excel user. I have access 2010. it seems the webservice() function from Excel started with excel 2013. I don't have anything 2013.
    You can call excel functions from Access. I have done it with simple functions in older versions.
    So you might find someone on this forum with more excel knowledge, and /or more webservice experience.

  9. #9
    Grefcon901 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    16
    Thank you @Orange for all your help. Everything Access eludes me, but doesn't the Access formula syntax change pretty dramatically from Excel?

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Well it isn't a formula as such. In access you would need to store the data in a table. But more importantly, you would have to set up a call to the Yahoo site with appropriate parameters; check that the request was successful; have some means to load the table.

    You have an intrinsic excel function (webservice) and a parameter.
    You would have to call the excel function, or build your own in Access/vba.

    I'm sure some of the readers here will know more. It's a matter of attracting their attention.

    Good luck.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have this done in excel but my problem is my Access ignorance TBH. Using the Yahoo Finance API through Excel is pretty simple, but can not find anyone who is doing this within Access. If anyone has time to walk me through how this is possible I would forever be in your debt.
    First, I have no interest in watching stocks. I pay people to do that.

    But it sounded interesting - so I gave it a go.

    I watched the youtube referenced in the thread - several times.

    Wasn't as simple as I thought.

    Learned about "WinHttp.WinHttpRequest.5.1"
    Struggled a lot, then learned how to spell. The ticker for Apple is "AAPL", NOT "APPL"!

    Finally got the request to return a CSV file.
    The CSV header has header names "Date", "Open" and "Close". I wanted to change the header before the CSV file was imported, because they are reserved words.
    But the data is separated with LF, but not a CR. So I couldn't use my normal method of opening the file:
    example
    Code:
            TF = FreeFile
            FileToOpen = CurrentProject.Path & "\" & FileToOpen
            Open FileToOpen For Input As #TF
    On to learn more about FSO.
    Finally was able to read existing file and create a 2nd CSV file to write to. All downhill now.

    Replace the header, read/write remaining lines, rename the files, kill the temp file.

    I used constants in the code so I wouldn't have to look for all the places to change a path/filename.
    The downloaded CSV file is, by default, saved in "C:". You can change the save location.


    There is a lot more that could be done:
    Error handling
    A check to ensure the start date is before the end date (and swap the dates if not - got bit on that more than once))
    A form to add ticker symbols
    In the youtube clip, the author had calculations, one of which was for Avg. % (I think). These calculations could be done in the form header/footer. Maybe need UDFs.

    Anyway, see if this helps.

    Enough of this - my little brain is tired
    Attached Files Attached Files

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Beautiful Steve. I always like to see people that like to learn. Great job.

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

  15. #15
    Grefcon901 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    16
    You out did yourself sir, thank you so much! I am just happy it is possible!! Im trying to get the VBA to run correctly right now.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 12-20-2015, 02:46 AM
  2. Replies: 7
    Last Post: 09-10-2015, 04:52 PM
  3. Replies: 1
    Last Post: 12-21-2011, 02:11 PM
  4. Record Retrieval/Modification Based On Entry
    By eddiebo924 in forum Forms
    Replies: 1
    Last Post: 06-19-2011, 06:41 PM

Tags for this Thread

Posting Permissions

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