Trong bài viết này, UniTrain sẽ chia sẻ cách sử dụng Excel và Power Query để tự tạo ra một công cụ theo dõi Portfolio đầu tư tiền điện tử rất đơn giản. Chúng ta sẽ đi từng bước, từ thiết lập Dashboard, tới việc sử dụng một API để kéo dữ liệu về giá của các mã coins về Excel, sau đó thêm một đoạn VBA nhỏ để khi mở Dashboard ra thì giá của các mã tiền điện tử sẽ được cập nhật một cách tự động.
1. Thiết lập Dashboard
Đầu tiên, chúng ta hãy đi xem qua Dashboard mà chúng ta sẽ thiết lập để có hình dung về cách thiết lập
Chúng ta sẽ có những thiết lập cơ bản như sau:
1. Danh sách các mã tiền điện tử bắt đầu từ ô C6 tới C8, các bạn có thể mở rộng vùng này ra nếu muốn.
2. Tương ứng với danh sách các mã tiền điện tử ở cột C, chúng ta sẽ có khối lượng tiền điện tử mỗi loại mà các bạn đang nắm giữ (hold) ở cột D.
3. Ở cột E, chúng ta sẽ có giá trị tương ứng của mỗi vị thế (Position) sẽ được cập nhật tự động thông qua công thức tham chiếu tới bảng giá thiết lập ở các bước tiếp theo. Các bạn có thể thiết lập cột E này đơn giản bằng hàm VLOOKUP. Trong file của mình, mình đã đặt tên bảng giá là price và định dạng vùng C5:E8 là table với tên table là portfolio, vậy nên, mình có thể sử dụng duy nhất một công thức như sau ở E6
Bởi vì vùng C5:C8 đã được định dạng theo dạng table, vậy nên, mỗi khi bạn thêm dữ liệu các mã tiền ảo mới và khối lượng vào cột C và cột D, thì công thức ở cột E sẽ tự động mở rộng.
4. Ô H6, chúng ta sẽ sử dụng công thức như sau để có thể tính tổng giá trị của Portfolio tiền ảo này.
5. Đối với các dòng từ 13 tới 15, thì bạn chỉ cần viết nội dung tĩnh trong ô Excel, sau đó tất cả các thông tin còn lại chúng ta sẽ cập nhật thông qua VBA.
2. Lấy dữ liệu từ sàn giao dịch tiền điện tử thông qua Power Query
Có rất nhiều sàn sẽ cho chúng ta dữ liệu qua kênh API để chúng ta có thể tạo ra được rất nhiều ứng dụng thú vị, trong bài viết này mình sẽ sử dụng API của sàn giao dịch tiền điện tử Binance để làm điều này, chúng ta sẽ sử dụng API Endpoints (URL) sau đây để có thể lấy dữ liệu từ Binance:
https://api.binance.com/api/v3/ticker/price
Để load được dữ liệu từ địa chỉ trên về Excel, chúng ta sẽ sử dụng Power Query trong Excel hoặc Power BI với tính năng From Web
- Trong Excel, bấm vào thẻ Data > Chọn From Web.
- Trong hộp thoại From Web tiếp theo, dán URL của Binance phía trên vào.
- Sau đó thực hiện các bước Transform dữ liệu trả về cụ thể như trong video để bạn có thể load dữ liệu về Sheets có price. M-Code của quá trình chuẩn bị dữ liệu này khá đơn giản như sau:
3. Làm mới dữ liệu mỗi khi mở file Excel
Để làm mới dữ liệu mỗi khi mở file Excel, các bạn hãy mở trình soạn thảo VBA ở trong Excel ra bằng cách bấm thẻ Developer > Visual Basic. Sau đó bấm vào ThisWorkbook trong cửa sổ Project và sử dụng đoạn code sau đây:
Như vậy, qua bài viết này, chúng ta đã học được cách kết hợp các kiến thức và công cụ để tạo ra một Dashboard giúp theo dõi giá trị portfolio đầu tư tiền ảo của chúng ta rất nhanh chóng. Các kiến thức này sẽ nằm trong các khóa học Excel và Power Query của UniTrain, các bạn hãy tham khảo để có được nền tảng kiến thức vững chắc hơn cho công việc và sự nghiệp nhé.
Nguồn: hocexcel.com
Xem thêm
Power Query là gì? Tại sao nhân viên văn phòng nên học Power Query?
Khóa học Ứng dụng Dashboard Reporting trong Excel