Tạo công cụ theo dõi Portfolio tiền điện tử với Excel và Power Query

Chuyên mục:
Power BI∙Power Query∙VBA

Xin chào, trong bài viết này Thanh và Học Excel Online sẽ chia sẻ với các bạn cách chúng ta có thể 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. Vì tính đơn giản của công cụ trong bài này, nên sẽ rất phù hợp với việc theo dõi giá trị Portfolio của các bạn đầu tư theo hướng mua và nắm giữ theo thời gian dài, chứ không phù hợp với các bạn thích trading. 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. Mình có làm một video hướng dẫn dành cho các bạn thích xem video ở cuối bài.

Xem nhanh

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:

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 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.
Ô 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 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:

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 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:

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 Sub

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 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: