vba时间换算_VBA中的汇率和货币换算
vba 时间换算
服务及其功能 (Services and their features)
Exchange rates can be obtained from many sources, some free, some paid.
汇率可以从许多来源获得，有的是免费的，有的是付费的。
Hardly two of these serve the same purpose or are targeted the same users. This means, that some research typically is necessary to pick the service that will fit a given scenario and demand. Several factors come into play:
其中⼏乎没有两个服务于相同的⽬的或针对相同的⽤户。 这意味着，通常需要进⾏⼀些研究才能选择适合给定场景和需求的服务。 有⼏个因素起作⽤：
authority - rates provided by national banks have high trust
授权-国家银⾏提供的利率具有⾼度信任
interface - what code is needed to retrieve the data
界⾯-检索数据需要什么代码
range of currencies - from and to which currencies is the demand for
货币的范围- 从和向哪个货币为所述需求
update frequency - once a day or more often
更新频率-每天⼀次或更频繁
costs - can a fee be accepted, or must the service be free to use
费⽤-可以接受费⽤，还是必须免费使⽤服务
Only one factor - code examples for the interfaces - we take care of here; the rest is up to you, and you will have to visit the various websites to obtain the current details and further info before making a decision.
只有⼀个因素-接⼝的代码⽰例-我们在这⾥处理； 其余的⼀切取决于您，您将需要访问各个⽹站以获
取当前的详细信息和更多信息，然后再做出决定。
提供的服务 (Services offered)
The services, that this project addresses, are:
该项⽬要解决的服务是：
1. The European Central Bank
1.欧洲中央银⾏
2. The Danish National Bank
2.丹麦国家银⾏
3. The Central Bank of the Russian Federation
3.俄罗斯联邦中央银⾏
4. Currency Converter API
4.货币转换器API
5. Currencylayer API
5. Currencylayer API
6. ExchangeRate API
7. Fixer
7.固定器
8. Open Exchange Rates
8.开放汇率
9. php.mk - National Bank of the Republic of North Macedonia
9. php.mk-北马其顿共和国国家银⾏
10. XE
10. XE
All services support the currencies commonly used in international trade; for more exotic currencies, you may be limited in the choice of service.
所有服务都⽀持国际贸易中常⽤的货币； 对于更多外来货币，您可能⽆法选择服务。
For free, a few services provide exchange rates from any base currency, some provide exchange rates based on one currency only, some only one or a few currencies based on any currency, and one provides exchange rates to one currency only (Euro, The European Central Bank). One service, XE, offers no free plan or subscription at all, only a seven-day trial.
免费提供⼀些服务，可以提供任何基础货币的汇率，⼀些服务仅提供基于⼀种货币的汇率，⼀些服务仅提供⼀种或⼏种基于任何货币的汇率，⽽⼀种服务仅提供对⼀种货币的汇率（欧元，欧洲中央银⾏）。 XE⼀项服务根本不提供免费计划或订阅，仅提供7天的试⽤期。
The exchange rates published by the services are what is called mid-market rates. This means, that they cannot be used for real transactions; for such, you must refer to the actual buying and selling rates of your bank or broker.
这些服务发布的汇率称为中端市场汇率 。 这意味着它们不能⽤于真实交易； 为此，您必须参考银⾏或经纪⼈的实际买卖价格。
功能 (Functions)
Like the services differ in offerings, so do the various APIs or download options, though only three basic techniques are used:
就像服务在提供的产品中有所不同⼀样，各种API或下载选项也是如此，尽管仅使⽤了三种基本技术：
1. addressing an API, delivering data as Json
解决API，以Json的形式传递数据
2. reading an XML document
读取XML⽂档
3. parsing an HTML document (web scraping, data extracting)
解析HTML⽂档（⽹络抓取，数据提取）
However, no two services - even using the same basic technique - offer the same data format; thus a custom function is required for each service.
但是，即使使⽤相同的基本技术，也没有两个服务提供相同的数据格式。 因此，每个服务都需要⾃定义功能。
The main functions offered are named:
提供的主要功能为：
ExchangeRatesXyz
where Xyz is a three-letter abbreviation of the service name.
Xyz是服务名称的三个字母的缩写。
Each of these functions returns an array with the rates, and also attempts to cache the download for two reasons:
这些函数中的每⼀个都会返回⼀个包含费率的数组，并出于两个原因⽽尝试缓存下载：
to speed up reading the rates multiple times
加快多次读取汇率
to save the usage of and the load on the service
以节省服务的使⽤和负载
The returned array is simple - with three or four dimensions of various data types:
返回的数组很简单-具有三个或四个维度的各种数据类型：
1. Publishing date (Date)
出版⽇期（⽇期）
2. ISO currency code (Three-letter string)
ISO货币代码（三字母字符串）
3. Exchange rate (Double)
汇率（双倍）
4. (Optional) Currency name (string)
（可选）货币名称（字符串）
Thus, a typical call will be:
因此，典型的调⽤将是：
Dim ArrayOfExhangeRates As Variant
ArrayOfExhangeRates = ExchangeRatesXyz()
The functions are supplemented with a set of matching functions for converting an amount from one currency to another. These are named in a similar way:
这些功能补充有⼀组匹配功能，⽤于将⾦额从⼀种货币转换为另⼀种货币。 这些以类似的⽅式命名：
CurrencyConvertXyz
CurrencyConvertXyz
These functions each utilise the output from the corresponding ExchangeRatesXyz function. Further, they cache the conversion factor for a set of currencies to speed up the calculation of many amounts between the same two currencies.
这些函数各⾃利⽤相应的ExchangeRatesXyz函数的输出。 此外，它们缓存⼀组货币的转换因⼦，以加快相同两种货币之间许多⾦额的计算。
The returned value is the conversion factor between the two passed currency codes, for example:
返回值是两个传递的货币代码之间的转换因⼦ ，例如：
Dim ConversionFactor As Double
ConversionFactor = CurrencyConvertXyz("BBB", AAA")
All functions support the neutral currency code XXX for an exchange rate of 1.
所有功能均⽀持1汇率的中性货币代码 XXX 。
(
)
早期或晚期绑定（32位或64位） (Early or late binding, 32- or 64-bit)
Where relevant, all functions support both early and late binding. Code has been tested with both 32-bit and 64-bit Microsoft Access 2016 and Access 365.
在相关情况下，所有功能均⽀持早期和晚期绑定。 代码已通过32位和64位Microsoft Access 2016和Access 365进⾏了测试 。
The Json modules from the project are required for those functions that retrieve data as Json.
那些将数据作为Json检索的功能需要项⽬中的Json模块。
服务内容 (The services)
1.欧洲中央银⾏ (1. The European Central Bank)
2.丹麦国家银⾏ (2. The Danish National Bank)
The ECB and The Danish National Bank offer a daily list of exchange rates for selected currencies, indeed all the European other than Euro. These can be downloaded as an XML file, but our functions
read them directly and transform them to an array in a few steps.
欧洲央⾏和丹麦国家银⾏提供特定货币的每⽇汇率清单，实际上是除欧元以外的所有欧洲货币。 这些可以作为XML⽂件下载，但是我们的函数可以直接读取它们，并通过⼏个步骤将它们转换为数组。
Note the use of static variables to prevent unnecessary repeated calls to the site. Effectively, the data will only be retrieved once per day. After the first call, the static array Rates, holding the exchange rates of the day, will be returned directly for all subsequent calls, speeding these up vastly.
请注意使⽤静态变量，以防⽌不必要的重复调⽤该站点。 实际上，每天仅检索⼀次数据。 第⼀次通话后，将为以后的所有通话直接返回保存当天汇率的静态数组Rates，从⽽⼤⼤加快了通话速度。
The in-line comments explain each step, for example for the ECB:
在线注释解释了每个步骤，例如针对欧洲央⾏：
' Retrieve the current exchange rates from the European Central Bank, ECB,
' for Euro having each of the listed currencies as the base currency.
' The rates are returned as an array and cached until the next update.
' The rates are updated once a day at about UTC 15:00.
'
' Source:
'  b.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html
'
' Note:
'  The exchange rates on the European Central Bank's website are indicative rates
'  that are not intended to be used in any market transaction.
'  The rates are intended for information purposes only.
'
' Example:
'  Dim Rates As Variant
'  Rates = ExchangeRatesEcb()
'  Rates(7, 0) -> 2018-05-30      ' Publishing date.
'  Rates(7, 1) -> "PLN"            ' Currency code.
'  Rates(7, 2) -> 4.3135          ' Exchange rate.
'
' 2018-06-07. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function ExchangeRatesEcb() As Variant
' Operational constants.
transform和convert的区别'
' Base URL for European Central Bank exchange rates.
Const ServiceUrl    As String = "b.europa.eu/stats/eurofxref/"    ' File to look up.
Const Filename      As String = "l"
' Update hour (UTC).
Const UpdateHour    As Date = #3:00:00 PM#
' Update interval: 24 hours.
Const UpdatePause  As Integer = 24
' Function constants.
'
' Async setting.
Const Async        As Variant = False
' XML node and attribute names.
Const RootNodeName  As String = "gesmes:Envelope"
Const CubeNodeName  As String = "Cube"
Const TimeNodeName  As String = "Cube"
Const TimeItemName  As String = "time"
Const CodeItemName  As String = "currency"
Const RateItemName  As String = "rate"
#If EarlyBinding Then
' Microsoft XML, v6.0.
Dim Document        As MSXML2.DOMDocument60
Dim XmlHttp        As MSXML2.ServerXMLHTTP60
Dim RootNodeList    As MSXML2.IXMLDOMNodeList
Dim CubeNodeList    As MSXML2.IXMLDOMNodeList
Dim RateNodeList    As MSXML2.IXMLDOMNodeList
Dim RootNode        As MSXML2.IXMLDOMNode
Dim CubeNode        As MSXML2.IXMLDOMNode
Dim TimeNode        As MSXML2.IXMLDOMNode
Dim RateNode        As MSXML2.IXMLDOMNode
Dim RateAttribute  As MSXML2.IXMLDOMAttribute
Set Document = New MSXML2.DOMDocument60
Set XmlHttp = New MSXML2.ServerXMLHTTP60
#Else
Dim Document        As Object
Dim XmlHttp        As Object
Dim RootNodeList    As Object
Dim CubeNodeList    As Object
Dim RateNodeList    As Object
Dim RootNode        As Object
Dim CubeNode        As Object
Dim TimeNode        As Object
Dim RateNode        As Object
Dim RateAttribute  As Object
Set Document = CreateObject("MSXML2.DOMDocument")
Set XmlHttp = CreateObject("MSXML2.ServerXMLHTTP")
#End If
Static Rates()      As Variant

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。