Thiết lập Dashboard
Đầu tiên, tất cả chúng ta hãy đi xem qua Dashboard mà tất cả chúng ta sẽ thiết lập để có tưởng tượng về cách thiết lập
Chúng ta sẽ có những thiết lập cơ bản như sau:
Bạn đang đọc: Tạo công cụ theo dõi Portfolio tiền điện tử với Excel và Power Query – Học Excel Online Miễn Phí
- 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.
- 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
- Ở 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
=XLOOKUP([@Symbol],price!A:A,price!B:B)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 tài liệu những 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 hóa lan rộng ra .
- Ô 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
=SUM(portfolio[Value]) - Đố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
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 tất cả chúng ta tài liệu qua kênh API để tất cả chúng ta hoàn toàn có thể tạo ra được rất nhiều ứng dụng mê hoặc, trong bài viết này mình sẽ sử dụng API của sàn thanh toán giao dịch tiền điện tử Binance để làm điều này, tất cả chúng ta sẽ sử dụng API Endpoints ( URL ) sau đây để hoàn toàn có thể lấy tài 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:
let Source = Json.Document(Web.Contents("https://api.binance.com/api/v3/ticker/price")), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"symbol", "price"}, {"symbol", "price"}) in #"Expanded Column1"
Làm mới dữ liệu mỗi khi mở file Excel
Để làm mới tài liệu mỗi khi mở file Excel, những 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 This Workbook trong hành lang cửa số Project và sử dụng đoạn code sau đây :
Private Sub Workbook_Open()
Dim lr
For Each cn In ThisWorkbook.Connections
cn.Refresh
Next
lr = Sheet2.Range("C100000").End(xlUp).Row + 1
Sheet2.Range("C" & lr).Value = Format(Now, "dd.MM.yyyy hh:mm:ss")
Sheet2.Range("D" & lr).Value = Sheet2.Range("H6").Value
Sheet2.Range("C14").Value = "Last updated at " & Format(Now, "dd.MM.yyyy hh:mm:ss")
End SubNhư 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 VBA và Power Query của Thanh, 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é.
File Excel kèm theo bài viết :
DOWNLOAD
Video kèm theo bài viết này :
Source: https://openlivenft.info
Category: TIN COIN
