Automatically get latest currency exchange rates into Access 97

I have been using this URL to get exchange rates from the web and get them into Access 97:

http://download.finance.yahoo.com/d/quotes.csv?s=USDCDN=X&f=l1

This gets me USD to CDN rates in a simple CSV file.

However, Yahoo just shut this down!!:

"It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com."

Does anyone know of another service that could work?
Thanks!
Russ :)
SOTAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
How about this webservice, it returns results in JSON, but that wouldn't be hard to parse and load into Access table.



»bp
0
SOTAAuthor Commented:
I saw that, but I have no idea how to use it or parse it into a simple number like "1.2355" for USD to CAD.
Any help would be much appreciated!!!
0
Bill PrewCommented:
Parsing JSON can be a little tricky in VBA, a weakness there.

Not sure exactly how you are downloading and loading today, but the datasets can be downloaded too in CSV and XML formats, links on the page below.

Euro foreign exchange reference rates


»bp
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bfuchsCommented:
See below on parsing JSON strings and inserting into Access tables.

https://www.experts-exchange.com/questions/29064282/JSON-import-in-Access-2016.html
1
SOTAAuthor Commented:
OK, using: https://api.fixer.io/latest?base=USD

I get this saved as an *.ini file on my local drive:

{"base":"USD","date":"2017-11-07","rates":{"AUD":1.307,"BGN":1.6916,"BRL":3.2712,"CAD":1.2767,"CHF":0.99957,"CNY":6.6381,"CZK":22.134,"DKK":6.4369,"GBP":0.76144,"HKD":7.8032,"HRK":6.5227,"HUF":268.98,"IDR":13528.0,"ILS":3.5144,"INR":64.995,"JPY":114.19,"KRW":1111.6,"MXN":19.089,"MYR":4.2231,"NOK":8.177,"NZD":1.4459,"PHP":51.398,"PLN":3.6668,"RON":3.9738,"RUB":58.851,"SEK":8.4279,"SGD":1.3641,"THB":33.13,"TRY":3.8638,"ZAR":14.172,"EUR":0.8649}}    

Now, how can extract the "CAD":1.2767 so I can use that exchange rate?
0
Gustav BrockCIOCommented:
You can download the rates as a zipped csv for ECB:

Current reference rates CSV (zip)

This you can download using the function DownloadFile  found here:

Show pictures directly from URLs in Access forms and reports

and unzip using the function UnZip found here:

Zip and unzip files and folders with VBA the Windows Explorer way

Then you can simply link that table and read the exchange rates you need.

I notice you use Access 97! Though outdated, it should work anyway, but I haven't tested that.

/gustav
1
Gustav BrockCIOCommented:
You can also use Json and the fixer.io api mentioned by Bill.

Retrieve the Json modules from here: VBA.CVRAPI
and import these in your Access database.

Run the test function using this URL:

ServiceUrl = "https://api.fixer.io/latest?base=USD&symbols=CAD"

Open in new window


in this function in module JsonTest:

' Call a Json service and return result as a collection and a messagebox.
'
Public Sub TestJsonService()

    Dim DataCollection      As Collection
    
    Dim ServiceUrl          As String
    Dim ResponseText        As String
    Dim UserAgent           As String
    
    Const Username          As String = "demo"
    Const App_id            As String = "b492b663ae3e458d9f0b042e8edb8c63"
    
    ' Register at http://www.geonames.org/login
    'ServiceUrl = "http://api.geonames.org/citiesJSON?north=44.1&south=-9.9&east=-22.4&west=55.2&lang=de&username=" & Username
    
    ' Register at https://openexchangerates.org/signup/free
    'ServiceUrl = "http://openexchangerates.org/api/latest.json?app_id=" & App_id
    
    'ServiceUrl = "http://cvrapi.dk/api?name=lagkagehuset&country=dk&format=json&version=0"

    ' ECB Exchange Rates via fixer.io.
    ServiceUrl = "https://api.fixer.io/latest?base=USD&symbols=CAD"

    UserAgent = "Example Org. - TestApp"
    
    If RetrieveDataResponse(ServiceUrl, ResponseText, UserAgent) = True Then
        Set DataCollection = CollectJson(ResponseText)
        MsgBox "Retrieved" & Str(DataCollection.Count) & " root member(s)", vbInformation + vbOKOnly, "Web Service Success"
    ElseIf ResponseText <> "" Then
        MsgBox ResponseText, vbCritical + vbOKOnly, "Web Service Error"
    End If
    
    Call ListFieldNames(DataCollection)
    
    Set DataCollection = Nothing
    
End Sub

Open in new window

Output will be:

root                        
    base                    USD
    date                    2017-11-07
    rates                   
        CAD                 1.2767

Open in new window

/gustav
1
SOTAAuthor Commented:
From all these GREAT ideas...I got it to work!!!
After saving the JSON file locally, I open it and search for "CAD" using Mid+InStr:
    Open CompanyProfile("ClientDatabaseFolderPath") & "\ExchangeRate.ini" For Input As #1    'Open file for input.
    Input #1, GetExchangeRate  
    Close #1    ' Close file.
    GetExchangeRate = Mid(strTextLine, InStr(strTextLine, "TRY") + 5, 6)

Open in new window

1
SOTAAuthor Commented:
Thanks everyone!!!
0
SOTAAuthor Commented:
Oops my code should have been:
GetExchangeRate = Mid(strTextLine, InStr(strTextLine, "CAD") + 5, 6)
0
Gustav BrockCIOCommented:
Great! Though you shouldn't have to save the Json first as a file.

/gustav
0
SOTAAuthor Commented:
Yes, good point Gustav!
I tried your code, but it does not work in Access 97.
There is no function "RetrieveDataResponse()" in Access 97.
0
SOTAAuthor Commented:
Sorry Gustav! I did not import the VBA into Access yet....my bad!
Will try again!
0
Gustav BrockCIOCommented:
Well, for a single tiny file, it won't make much of a difference ...

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.