Microsoft Access
Microsoft Access

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: 2017-11-07

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium. Start your 7-day free trial.

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.

LVL 62

Bill PrewCommented: 2017-11-07

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

Foreign exchange rates and currency conversion JSON API

»bp

SOTAAuthor Commented: 2017-11-07

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!!!

LVL 62

Bill PrewCommented: 2017-11-07

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

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

LVL 5

bfuchsCommented: 2017-11-07

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

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

SOTAAuthor Commented: 2017-11-07

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?

LVL 54

Gustav BrockCIOCommented: 2017-11-07

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

LVL 54

Gustav BrockCIOCommented: 2017-11-07

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