We help IT Professionals succeed at work.

Automatically get latest currency exchange rates into Access 97

SOTA
SOTA asked
on
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 :)
Comment
Watch Question

View Solutions Only

Bill PrewTest your restores, not your backups...
Top Expert 2016

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


»bp
SOTA

Author

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!!!
Test your restores, not your backups...
Top Expert 2016
Commented:
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
bfuchs
Commented:
See below on parsing JSON strings and inserting into Access tables.

https://www.experts-exchange.com/questions/29064282/JSON-import-in-Access-2016.html
SOTA

Author

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?
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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
SOTA

Author

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

SOTA

Author

Commented:
Thanks everyone!!!
SOTA

Author

Commented:
Oops my code should have been:
GetExchangeRate = Mid(strTextLine, InStr(strTextLine, "CAD") + 5, 6)
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great! Though you shouldn't have to save the Json first as a file.

/gustav
SOTA

Author

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.
SOTA

Author

Commented:
Sorry Gustav! I did not import the VBA into Access yet....my bad!
Will try again!
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, for a single tiny file, it won't make much of a difference ...

/gustav