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

Xin chào các bạn, 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 một công cụ theo dõi Danh mục đầu tư Cryptocurrency rất đơn giản. Vì tính đơn giản của công cụ trong bài viết này sẽ rất phù hợp để theo dõi giá trị Portfolio của bạn đang đầu tư theo hướng mua và nắm giữ lâu dài, không phù hợp với các bạn thích giao dịch. Chúng tôi sẽ thực hiện từng bước, từ thiết lập Trang tổng quan, đến sử dụng API để kéo dữ liệu giá của đồng tiền sang Excel, sau đó thêm một đoạn mã VBA nhỏ để khi Trang tổng quan được mở, giá của tiền điện tử sẽ được hiển thị . sẽ được cập nhật tự động. Mình có làm video hướng dẫn cho các bạn thích xem video ở cuối bài.

Thiết lập trang tổng quan

Trước tiên, hãy xem qua Trang tổng quan mà chúng tôi sẽ thiết lập để có ý tưởng về cách thiết lập

cong-cu-theo-doi-portfolio-binance-tien-dien-tu-01

Chúng tôi sẽ có các cài đặt cơ bản sau:

Tải game crack việt hoá tại: https://daominhha.com
  1. Danh sách tiền điện tử bắt đầu từ ô C6 đến C8, bạn có thể mở rộng khu vực này nếu muốn.
  2. Tương ứng với danh sách tiền điện tử trong cột C, chúng tôi sẽ có khối lượng của từng loại tiền điện tử mà bạn đang nắm giữ (giữ) trong cột D
  3. Tại cột E, chúng ta sẽ có giá trị tương ứng của từng vị trí (Position) sẽ được cập nhật tự động thông qua công thức tham khảo bảng giá đã thiết lập ở các bước tiếp theo. Bạn có thể thiết lập cột E này đơn giản bằng hàm VLOOKUP. Trong tệp của tôi, tôi đã đặt tên bảng giá là giá bán và định dạng vùng C5: E8 dưới dạng một bảng với tên bảng danh mục đầu tư, vì vậy, tôi chỉ có thể sử dụng một công thức như thế này trong E6
    =XLOOKUP([@Symbol],price!A:A,price!B:B)

    Bởi vì khu vực C5: C8 đã được định dạng dưới dạng bảng, mỗi khi bạn thêm tiền điện tử mới và dữ liệu khối lượng vào cột C và cột D, công thức trong cột E sẽ tự động mở rộng.

  4. Trong ô H6, chúng ta sẽ sử dụng công thức sau để tính tổng giá trị của Danh mục đầu tư tiền ảo này
    =SUM(portfolio[Value])
  5. Đối với dòng 13 đến dòng 15, bạn chỉ cần ghi nội dung tĩnh vào ô Excel, sau đó tất cả phần còn lại chúng tôi sẽ cập nhật qua VBA.
>>> Xem thêm:  Power Automate Desktop là gì, ứng dụng thực tế của Power Automate Desktop

Nhận dữ liệu từ các sàn giao dịch tiền điện tử thông qua Power Query

Có rất nhiều sàn giao dịch sẽ cung cấp cho chúng ta dữ liệu thông qua kênh API để chúng ta có thể tạo ra 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 mã hóa Binance để làm việc này, chúng ta sẽ sử dụng API Endpoints sau (URL ) để có thể lấy dữ liệu từ Binance:

https://api.binance.com/api/v3/ticker/price

Để tải dữ liệu từ địa chỉ trên xuống Excel, chúng tôi sẽ sử dụng Power Query trong Excel hoặc Power BI với tính năng Từ Web

  1. Trong Excel, hãy bấm vào tab Dữ liệu > Chọn Từ Web
  2. Trong hộp thoại Từ web Tiếp theo, dán URL Binance ở trên
    cong-cu-theo-doi-portfolio-binance-tien-dien-tu-02
  3. Sau đó thực hiện các bước Biến đổi dữ liệu trả về cụ thể như trong video để có thể nạp dữ liệu vào Trang tính có giá. Mã M 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 bạn mở tệp Excel

Để làm mới dữ liệu mỗi khi bạn mở tệp Excel, vui lòng mở trình chỉnh sửa VBA trong Excel bằng cách nhấp vào tab Nhà phát triển> Visual Basic. Sau đó nhấp vào ThisWorkbook trong cửa sổ Dự án và sử dụng mã sau:

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, thông qua bài viết này, chúng ta đã học được cách kết hợp kiến ​​thức và công cụ để tạo ra một bảng điều khiển giúp theo dõi giá trị danh mục đầu tư tiền điện tử một cách rất nhanh chóng. Những kiến ​​thức này sẽ có trong khóa học VBA và Power Query của Thành, hãy tham khảo để có được nền tảng kiến ​​thức vững vàng hơn cho công việc và sự nghiệp.

>>> Xem thêm:  Hướng dẫn cách sửa lỗi Circular References Warning trong Excel 2010, 2013, 2016

File Excel đính kèm bài viết:

TẢI XUỐNG

Video đi kèm bài viết này:

Video trên YouTube

Nguồn : Blog hocexcel