排骨干货节 篇三：Visa淘金计划#技术宅拯救海淘, 用 Google 版 Excel 比价亚马逊商品 下篇

一. 续言

上一篇我们已经可以根据商品的 URL 取得价格了, 这一篇我们将把汇率转换实现出来, 这样所以价格都变为人民币就可以比价了.

除了加入汇率转换外, 我们还会增加下拉选项, 组合生成商品 URL, 图表生成等功能.

二. 货币汇率获取与价格转换

我们可以从网上免费获取每日的货币汇率, 然后把不同货币单位的价格统一转成人民币价格.

1. 选择免费的汇率转换 API.

之前的很多年里, 大家都用 Yahoo 的 download.finance.yahoo.com 来取汇率, 排骨写这此文时发现无法使用了. 排骨临时找了个 free.currencyconverterapi.com.

这个 API 使用很简单. 只要更改 URL 后面的货币代码, 就可以获取指定货币的汇率. 如上图美元兑人民币结果是 6.765804 (一个月涨了10%??). 能动手就不吵吵, 取汇函数写起来.

上面是个简单的获取美元-人民币转换汇率函数. 当然这不是最终的函数, 后面我们还需要判断价格的货币单位选择对应的货币转换, 这样才能支持殴元/英磅/日元等价格.

2. 判断货币单位.

亚马逊商品价格的货币单位显示很随意. 美亚用 $, 英亚用 £, 中亚日亚用 ￥, 欧元用 EUR, 加拿大元用 CDN$. 好在货币单位都在价格信息的左边. 我们需要写个函数把这些货币单位转换成汇率 API 支持的格式.

上图是排骨整理好的亚马逊各国价格信息格式. 很容易看出除了货币单位不同以外, 千分符和小数点也有区别, 日元没有角分所以没有小数. 我们需要写一个货币解析函数将分隔货币单位和数值, 货币也要换成标准的3个英文字符.

澳大利亚墨西哥与美元无法只用货币单位区别, 印度亚马逊使用图片显示的卢比, 这三个先不管它.

3. 将所有价格转换成人民币.

汇率有了, 货币单位有了, 价格数值也有了. 我们只需要小小修改一下上面写的取汇函数, 再配合货币解析函数, 就能立即计算出对应的人民币价格了.

日亚的反爬虫机制比较严格, Google Sheets 多次尝试后就自动放弃了.

至此比价功能算是基本完成了.

三. 实现比价表格更完整的形式

完成比价后, 我们还有什么可以完善的吗?
让我们通过传统的 Excel 功能来把这个比价表格变得更强大一些.

1. 获取商品页面 URL.

搜索 'lego 31058', 的到我们需要的商品连接.

2. 清理 URL, 获取商品 Id.

我们试着把 URL 右边的 Path 和 Query String (额外参数) 去掉后, 发现仍然能正经打开商品页面. 然后把 URL 中间的 "LEGO-Creator-Dinosaurs-31058-Dinosaur" 也去掉, 仍然不影响页面的打开.

几次尝试后, 最终把 URL 精减为 https://www.amazon.com/dp/B01KJEOCDW. 其中 dp 代表商品页面, B01KJEOCDW 是商品 Id.

下面 3 行分别是京东/天猫/淘宝的商品页面 URL, 都是用的 item 代表商品页面, 数字编号作为商品 Id.

https://item.jd.com/4617714.html
https://detail.tmall.com/item.htm?id=547160204244
https://item.taobao.com/item.htm?id=548124062132

3. 发现店家 (卖家) Id.

再看看上篇中我们取得的三个商品页 URL.

Amazon US: https://www.amazon.com/gp/product/B01KJEOCDW/ref=ox_sc_act_title_1?smid=ATVPDKIKX0DER&psc=1
Buy 4 Less Shop: https://www.amazon.com/gp/product/B01KJEOCDW/ref=ox_sc_act_title_1?smid=A244NFQODTIILZ&psc=1
ToyWiz: https://www.amazon.com/gp/product/B01KJEOCDW/ref=ox_sc_act_title_1?smid=AJF0HB30PBJAV&psc=1

不难发现三个 URL 的唯一区别在于 smid 参数不同, 这个 smid 就是店家 Id! 亚马逊自营店也有自己的 smid. (排骨提示: smid 也可以写作 me 或 m)

最终三个 URL 被精简成上面这样.

4. 各国亚马逊域名切换

上一步我们把商品 URL 精简到了最短, 可以直接替换商品 Id 和 店家 Id 获得到不同商品和在不同店家出售的 URL. 为什么不再把国家切换也加进去?

在表格中新加一个名称叫 Region 的 sheet, 加入各国缩写和域名. 然后建了两个 Named Range 备用.

新建一个 Region 列 (A列), 鼠标右击 A2, 选择 Data validation.

填入刚才定义 Named Range 'Regions'. 这样 A2 格就有下拉框选择国家缩写的功能了.

然后进行下面 3 个步骤, 整个 Region 列就都有下拉框选择功能了.

1. 选中 A2, 再按 Ctrl+C 复制.
2. 拉选 A3~An, 再按鼠标右键.
3. 选择 Paste special -> Paste data validation only.

然后再加一列 Domain, 用 VLOOKUP 函数通过左边 Region 列的值获取对付国家的亚马逊域名.

这样国家域名切换就搞定了.

5. 整合所有步骤, 完成强大比价表格.

按上一步的过程, 我们把收集到的店家 Id 也独立到一个 'Seller' 表里. 同样也定义两个名称, 一个给 VLOOKUP 用, 一个给 Data Validation 用.

然后与第4步完整相同的方法完成 Seller 和 Seller Id 列.

接着我们把 '乐高 31058' 的商品 Id 放入一个 Product Id 列中, 再用字符串拼接函数 CONCATENATE 把 Domain, Seller Id, Product Id 拼成最终的商品页 URL.

Domain 和 Seller Id 并没有什么显示的意义, 还是去掉比较好.

强力的比价表格就完成啦.

四. 图表生成

最后我们加一个条形图表来更直观一点的比较价格差异.

1. 从 Insert 菜单中选择 Chart.

一个如上图般大小的空白图表就出现在表格中了.

2. 选择图表, 点右上角的 ... 然后选择 Edit chart...

3. 设置图表数据项和样式.

4. 图表完成.

五. 总结

写了两篇文章终于把一个简单的比价表格完成了! 不知道同学们读完后的感想是什么样的. 作为作者的排骨的预期其实有下面几点:

1. 使用免费的技术手段完成机械的人工的劳动. (自动化 -> 智能化?)
2. 整合常用的工具与简单的编程实现独特的功能. (工具活用 ->系统集成?)
3. 分析网站规则, 实现数据截取与整理. (数据分析 -> 数据挖掘?)
4. 通过自己的努力, 省钱. (省钱就是赚钱!)

以上4点重要性从小到大排列.

本文中的 Google Sheets 表格文件排骨已经共享出来了. 共享连接如下. 不会编程或者还没完全弄明白的同学, 可以直接打开边用边学.

另外本文中忽略的澳大利亚, 墨西哥, 印度三国亚马逊的价格处理问题, 就留给同学们自己分析和解决啦.

会用 Excel + VBA 的同学也可以试着做一个纯 Excel 版本跟大家分享一下. 当然能用 Excel + JavaScript 做出来就更好了.

希望大家能分享更多更好的创意, 多一些原创达人, 少一些调参师, 调包侠.

感谢阅读, 看到错字错词请主动脑补.