Avatar of SOTA
SOTA
Flag for Canada asked on

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 :)
Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
Bill Prew

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



»bp
SOTA

ASKER
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!!!
ASKER CERTIFIED SOLUTION
Bill Prew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
bfuchs

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOTA

ASKER
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOTA

ASKER
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

ASKER
Thanks everyone!!!
SOTA

ASKER
Oops my code should have been:
GetExchangeRate = Mid(strTextLine, InStr(strTextLine, "CAD") + 5, 6)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

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

/gustav
SOTA

ASKER
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

ASKER
Sorry Gustav! I did not import the VBA into Access yet....my bad!
Will try again!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gustav Brock

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

/gustav