After cross-border e-commerce sellers set up stores on sites in different countries, they will find that in addition to the inconsistent time, the expression of currency and amount is also inconsistent in different sites, which is not only reflected in the number format, but also includes the specific location of the currency symbol and the amount splitter.
Therefore, as an operator, we must convert the amount format and exchange rate in advance, unify the way all amounts are expressed, and convert ** RMB according to the exchange rate, so as to ensure that the final financial data is accurate.
Today, Xiao Jiu will introduce to you the tips on how to calculate the unified processing of currency amounts and exchange rate conversion.
The amount of money displayed varies from country to country, and there are several different ways to express it:
A comma is used as a thousandth symbol, while a decimal point is represented by a solid dot, such as 1,00000 (also applicable to the United Kingdom and the United States).
Use a solid dot as the sign for the thousandth place and a comma as the decimal point, e.g. 1000,00 (most European countries, Brazil, etc.).
Use a space as the symbol for the thousandth, e.g. 1 000,00 (Spain, Portugal, Italy, etc.).
Different from the complexity of time processing, for different regional expressions, the number of cross-border BI only needs a formula, which can be used for the conversion of currency amounts in all countries:
substitute(substitute($,",","."),"−","-")
As for how to calculate the exchange rate conversion, it is also a special headache for many companies, because different companies deal with the exchange rate differently.
Month-end exchange rate.
A more common way to calculate is to quote the exchange rate of the first or last business day of each month as the exchange rate of the current month.
The average exchange rate of the stage.
There are also companies that will use the method of fixed exchange rate for the whole year or the average fixed exchange rate for half a year and three months, which is the exchange rate settlement method of mainstream foreign trade. Generally speaking, in order to avoid forward exchange rate fluctuations, the foreign trade company will sign an agreement with the bank to lock in the total amount in the next month, and the exchange rate will be locked on the premise that the agreement does not exceed a certain amount.
Daily real-time exchange rates.
There are even companies that use the daily exchange rate to match the sales of the day for calculation.
Considering the diverse needs, several cross-border BIs provideA daily exchange rate table against the Chinese yuan in currency unitsThe Bank of China conversion price of different currencies of the day is updated daily, which can be directly merged into the analysis, which solves the trouble of statistical exchange rates.
1.Since the currencies used in the analysis are abbreviated in English, we need to maintain a currency name mapping table
2.The daily exchange rate of the Bank of China collected by the cross-border BI is merged left and right through the common field currency name.
3.Calculate the exchange rate of different currencies against RMB against US dollar as required.
If you're using a daily exchange rate, you can use it directly by matching the release date and the sales date here.
4.Finally, we filter by taxonomy:
The earliest time of the month and the latest time to get the exchange rate at the beginning of the month and the end of the month.
The average of the exchange rate against the Chinese yuan against the US dollar yields the average monthly exchange rate.
The release date in the category can be adjusted freely, for example, changing the year to the adult quarter to get the quarterly average exchange rate.
The above is the currency system.
1. A little trick on how to calculate the exchange rate conversion was shared, I hope it will be helpful to everyone!
Amount format processing is a very basic skill, and the amount expressed in all data can be easily unified through a one-step formula of cross-border BI to ensure the accuracy of subsequent analysisExchange rate processing is also one of the problems that annoys many sellers, and the real-time capture of exchange rate tables across borders can greatly solve the troubles caused by exchange rates.
As a BI tool, all analysis steps can be reused with one click, and a one-time field cleaning process can be built, and the ** table needs to be replaced in the follow-up to be automatically updated, which is convenient and fast.