Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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 :)
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 :)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
How about this webservice, it returns results in JSON, but that wouldn't be hard to parse and load into Access table.
»bp
»bp
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!!!
Any help would be much appreciated!!!
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
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
See below on parsing JSON strings and inserting into Access tables.
https://www.experts-exchange.com/questions/29064282/JSON-import-in-Access-2016.html
https://www.experts-exchange.com/questions/29064282/JSON-import-in-Access-2016.html
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
Now, how can extract the "CAD":1.2767 so I can use that exchange rate?
I get this saved as an *.ini file on my local drive:
{"base":"USD","date":"2017
Now, how can extract the "CAD":1.2767 so I can use that exchange rate?
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
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
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:
in this function in module JsonTest:
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"
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
root
base USD
date 2017-11-07
rates
CAD 1.2767
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:
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)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial