It Seems that Jwen Bought Macau Dollars at Wrong Time ...

It Seems that Jwen Bought Macau Dollars at Wrong Time ...

I bought foreign exchange for my clients several time during my intership in ICBC. Recently, Jwen brought a lot of Macau dollars in order to pay tuition fees. So I want to the foreign exchange price of Macau dollars of the bank of China and see if I can save some money for Jwen.

Some common sense of Foreign Exchange

Open the Bank of China Foreign Exchange Rate website and you can see that there are five kinds of price listed:

Obviously:

  1. Price of Purchasing Spot Exchange > Price of Purchasing Foreign Cash;
  2. Price of Selling Spot Exchange < Price of Selling Foreign Cash;
  3. The converted price of BOC is about the average of the other four prices.

It should be noted that if we need to buy foreign currency, we should pay attention to the price of Selling Spot Exchange and the price of Selling Foreign Cash. Purchasing and selling here are for banks. Spot exchange and Cash here refer to foreign spot exchange and foreign cash. It is also easy to understand the spot selling price is lower that the cash selling price. There are cost expenditures such as escort, security and so on in the transportation of paper money.

Next, crawl the data:

R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
library(httr)
library(rvest)
library(tibble)
library(httr)
library(progress)
library(readr)
library(lubridate)
# crawl the first page
html <- GET("http://srh.bankofchina.com/search/whpj/search.jsp?erectDate=2018-01-01&nothing=2019-08-09&pjname=1327&page=1",
add_headers(c()),
set_cookies(c()))

# get the total page numbers
recordnum <- html %>%
content() %>%
html_nodes(xpath = '/html/body/div/script[2]/text()') %>%
as.character() %>%
stringr::str_extract(pattern = "m_nRecordCount = (.*);") %>%
stringr::str_extract(pattern = "\\d+") %>%
as.numeric()
pagenum <- round(recordnum / 20)

# loops, get the first table, than append others
pb <- progress_bar$new(total = pagenum)
df <- html %>%
content() %>%
html_table(fill = TRUE) %>%
.[2] %>%
.[[1]] %>%
.[,1:7] %>%
as_tibble() %>%
dplyr::filter(!is.na(`现汇买入价`))

for(i in 2:pagenum){
temphtml <- GET(paste0("http://srh.bankofchina.com/search/whpj/search.jsp?erectDate=2018-01-01&nothing=2019-08-09&pjname=1327&page=", i),
add_headers(c()),
set_cookies(c()))
tempdf <- temphtml %>%
content() %>%
html_table(fill = TRUE) %>%
.[2] %>%
.[[1]] %>%
.[,1:7] %>%
as_tibble() %>%
dplyr::filter(!is.na(`现汇买入价`))
df <- rbind(df, tempdf)
pb$tick()
}

df <- df %>%
mutate(
`发布时间` = lubridate::ymd_hms(`发布时间`)
) %>%
arrange(`发布时间`)

# save
write_rds(df, "今年澳门元的牌价.rds")

Because there are only 20 observations per page, BOC will quote about 120 prices per day. So, to get one day’s data, you need crawl 6 pages. I only crawled data from January 1st, 2018 to today. A total of 84,418 records were obtained. It takes about 25 minutes.

If you dont want to waste time on the loops, you can just download my results:





Macau Dollar Exchange Rate

First of all, let we look at the changing trend of the selling price of Macau dollars over the past two years:

R
1
2
3
4
5
6
7
8
9
rm(list = ls())
df <- read_rds("今年澳门元的牌价.rds")
df %>%
ggplot(aes(x = `发布时间`, y = `现汇卖出价`)) +
geom_line() +
scale_x_datetime(labels = scales::date_format()) +
labs(title = paste0(df$货币名称[1], "牌价"),
subtitle = "数据来源:中国银行",
caption = "注:对于文亮同学来说,现汇卖出价越低约值得买入澳元")

It can be seen that Jwen’s purchase of Macau dollars in the past few days is … cashed at the highest point!

The foreign exchange market is a $7 \times 24$ market, so guess that foreign qutations may vary at different times:

R
1
2
3
4
5
6
7
8
9
10
11
df %>%
mutate(
hour = hour(发布时间)
) %>%
arrange(hour) %>%
ggplot(aes(x = hour, y = `现汇卖出价`)) +
geom_point(position = position_jitter(0.5), alpha = 1/50) +
geom_smooth() +
labs(x = "发布时刻", caption = "注:似乎上午去银行换外汇更划算",
title = "中国银行澳门元牌价与发布时刻的关系",
subtitle = "2018年1月1日——2019年8月9日")

During the day, prices are fairly uniform, so it’s almost the same whenerve to go to the bank to buy Macau dollars.

Finally, let’s look at the relationship between the five prices.

R
1
2
3
4
5
6
7
8
9
10
11
12
13
library(tidyr)
df %>%
gather(现汇买入价, 现钞买入价, 现汇卖出价, 现钞卖出价, 中行折算价, key = "term", value = "price") %>%
ggplot(aes(x = 发布时间, y = price, group = term)) +
geom_point(aes(color = term),
position = position_jitter(width = 0.2),
alpha = 1/100) +
geom_smooth(aes(color = term)) +
scale_x_datetime(labels = scales::date_format()) +
scale_color_brewer(palette = "Set2") +
labs(y = "价格", color = "价格种类",
title = "中国银行五种牌价的关系",
caption = "数据来源:中国银行")

Other currency

The request link for Macau dollars is:

1
http://srh.bankofchina.com/search/whpj/search.jsp?erectDate=2018-01-01&nothing=2019-08-09&pjname=1327&page=1
  • erectDate:Start Date;
  • nothing:End Date;
  • pjname:Currency ID;
  • page:Page Number.

Currency Number Cross-references:

ID Currency
1314 英镑
1315 港币
1316 美元
1317 瑞士法郎
1318 德国马克
1319 法国法郎
1375 新加坡元
1320 瑞典克朗
1321 丹麦克朗
1322 挪威克朗
1323 日元
1324 加拿大元
1325 澳大利亚元
1326 欧元
1327 澳门元
1328 菲律宾比索
1329 泰国铢
1330 新西兰元
1331 韩元
1843 卢布
2890 林吉特
2895 新台币
1370 西班牙比塞塔
1371 意大利里拉
1372 荷兰盾
1373 比利时法郎
1374 芬兰马克
3030 印尼卢比
3253 巴西里亚尔
3899 阿联酋迪拉姆
3900 印度卢比
3901 南非兰特
4418 沙特里亚尔
4560 土耳其里拉

unsplash-logozhao chen

# R

Comments

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×