kiến thức [Excel VBA] Lấy dữ liệu từ trang web bất kỳ

NguyenDang95 · 22/7/23

Xin chào mọi người. Trong bài viết này mình xin giới thiệu đến mọi người cách thức lấy dữ liệu từ trang web bất kỳ. Trong Excel, người dùng có thể sử dụng công cụ có sẵn Power Query để lấy dữ liệu từ một trang web bất kỳ, dữ liệu trả về thông thường là HTML (ở dạng bảng), XML hoặc JSON. Tuy vậy người dùng lại không thể tinh chỉnh những loại dữ liệu nào muốn nhận hoặc chỉ muốn dữ liệu theo nhiều tiêu chí khác nhau (tham số truy vấn (query parameters) của URL hoặc Request Body), lúc này VBA sẽ là lựa chọn hoàn hảo.
Chúng ta xét một ví dụ thực tế dưới đây.
Ví dụ: Lấy dữ liệu chứng khoán theo danh mục từ trang web iboard.ssi.com.vn:
Giả sử người dùng muốn lấy dữ liệu từ trang web iboard.ssi.com với những danh mục như VN100,VN30,VNCONS:

Để tìm được nguồn cung cấp dữ liệu cho bảng trên, chúng ta thực hiện những bước sau:

Từ trình duyệt web nhân Chromium bất kỳ (Edge, Chrome, Opera, v.v.), người dù nhấn phím F12 để mở Developer Tools
Tiếp theo, chọn tab Network - Fetch/XHR, từ đây người dùng có thể tìm được nguồn dữ liệu trên bắt nguồn từ API nào.
Dựa vào thông tin của các tab Headers, Payload và Response, người dùng tiến hành viết macro VBA tạo yêu cầu HTTP (HTTP Request) và xử lý phản hồi (Response) nhận được để trình bày dữ liệu ra Excel

Kết quả:

Người dùng có thể tìm thấy tệp Excel chứa macro VBA đính kèm theo bài viết này.

Cập nhật:
Alo các thím, tình hình là tệp Excel mẫu ở đầu bài viết không còn sử dụng được nữa do SSI đã thay đổi API, cho nên mình quyết định viết lại hoàn toàn dưới dạng VSTO add-in để mọi người có thể tiện sử dụng.

Bổ sung: Ngoài việc lấy/cập nhật dữ liệu theo cách thủ công trên, mọi người có thể lấy/cập nhật dữ liệu theo thời gian thực thông qua hàm RTD của Excel.

Đầu tiên, mọi người tải tệp Ssi-Rtd.zip đính kèm theo bài viết này và tiến hành giải nén ra.
Xác định các thím đang sử dụng phiên bản Excel 32 bit hay 64 bit, nếu 32 bit thì dùng thư mục x86, 64 bit thì dùng thư mục x64.
Mở Start - Gõ "cmd" và nhấp chuột phải chọn Run as Administrator để mở trình nhắc lệnh Cmd với quyền quản trị viên.
Tiến hành gõ lệnh như sau:

Mã:

x86:
C:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe "đường dẫn đến tệp .dll" /tlb /codebase
x64:
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe "đường dẫn đến tệp .dll" /tlb /codebase

Mở tập tin Ssi-Rtd.xlsx và xem kết quả.
Mô phỏng:

Cập nhật: Bổ sung thêm nguồn dữ liệu cho hàm RTD cho phép lấy dữ liệu thời gian thực từ trang Fpts.

NguyenDang95 · 22/7/23

(Bổ sung thêm) Ngoài ra nếu người dùng có tài khoản Microsoft 365 doanh nghiệp hoặc trường học thì có thể sử dụng Office Scripts thay cho VBA mà hiệu quả vẫn tương đương.
Chúng ta viết một script với nội dung như sau:

JavaScript:

async function main(workbook: ExcelScript.Workbook) {
  const worksheet = workbook.getWorksheet("GetStockRealtimesByGroup");
  clearData(worksheet);
  const categories = worksheet.getRange("B2").getValue().toString();
  const categoriesList = categories.split(",");
  let rowCount = 0;
  let lastRow = worksheet.getRange("A1:Y5").getRowCount();
  for (let category of categoriesList) {
    const result = await getResult(category);
    rowCount = result.length;
    if (rowCount > 0) {
      worksheet.getRangeByIndexes(lastRow, 0, rowCount, 25).setValues(result);
    }
  };
}

function clearData(worksheet: ExcelScript.Worksheet) {
  const lastRow = worksheet.getUsedRange().getRowCount();
  if (lastRow > 5) {
    let dataRange = worksheet.getRangeByIndexes(6, 0, lastRow, 25);
    dataRange.delete(ExcelScript.DeleteShiftDirection.up);
  }
}

async function getResult(category: string) {
  const requestBody = { "operationName": "stockRealtimesByGroup", "variables": { "group": category }, "query": "query stockRealtimesByGroup($group: String) {\n  stockRealtimesByGroup(group: $group) {\n    stockNo\n    ceiling\n    floor\n    refPrice\n    stockSymbol\n    stockType\n    exchange\n    lastMatchedPrice\n    matchedPrice\n    matchedVolume\n    priceChange\n    priceChangePercent\n    highest\n    avgPrice\n    lowest\n    nmTotalTradedQty\n    best1Bid\n    best1BidVol\n    best2Bid\n    best2BidVol\n    best3Bid\n    best3BidVol\n    best4Bid\n    best4BidVol\n    best5Bid\n    best5BidVol\n    best6Bid\n    best6BidVol\n    best7Bid\n    best7BidVol\n    best8Bid\n    best8BidVol\n    best9Bid\n    best9BidVol\n    best10Bid\n    best10BidVol\n    best1Offer\n    best1OfferVol\n    best2Offer\n    best2OfferVol\n    best3Offer\n    best3OfferVol\n    best4Offer\n    best4OfferVol\n    best5Offer\n    best5OfferVol\n    best6Offer\n    best6OfferVol\n    best7Offer\n    best7OfferVol\n    best8Offer\n    best8OfferVol\n    best9Offer\n    best9OfferVol\n    best10Offer\n    best10OfferVol\n    buyForeignQtty\n    buyForeignValue\n    sellForeignQtty\n    sellForeignValue\n    caStatus\n    tradingStatus\n    remainForeignQtty\n    currentBidQty\n    currentOfferQty\n    session\n    tradingUnit\n    __typename\n  }\n}\n" };
    const response = await fetch('https://wgateway-iboard.ssi.com.vn/graphql', {
        method: "POST",
        headers: {
            "Content-Type": "application/json",
            "Content-Length": JSON.stringify(requestBody).length.toString()
        },
        body: JSON.stringify(requestBody)
    });
    if (response.ok) {
      const json: GetStockRealtimesByGroupResponse = await response.json();
      const result: (string | number)[][] = [];
      json.data.stockRealtimesByGroup.forEach(item => {
        result.push([category, item.stockSymbol, item.ceiling, item.floor, item.refPrice, item.best3Bid, item.best3BidVol, item.best2Bid, item.best2BidVol, item.best1Bid, item.best1BidVol, item.matchedPrice, item.matchedVolume, item.best1Offer, item.best1OfferVol, item.best2Offer, item.best2OfferVol, item.best3Offer, item.best3OfferVol, item.nmTotalTradedQty, item.highest, item.lowest, item.buyForeignQtty, item.sellForeignQtty, item.remainForeignQtty]);
      });
      return result;
    }
}

interface GetStockRealtimesByGroupResponse {
  data: Data
}

interface Data {
  stockRealtimesByGroup: Array<stockRealtimesByGroup>
}

interface stockRealtimesByGroup {
  stockNo: string
  ceiling: number
  floor: number
  refPrice: number
  stockSymbol: string
  stockType: string
  exchange: string
  lastMatchedPrice: number
  matchedPrice: number
  matchedVolume: number
  priceChange: string
  priceChangePercent: string
  highest: number
  avgPrice: number
  lowest: number
  nmTotalTradedQty: number
  best1Bid: number
  best1BidVol: number
  best2Bid: number
  best2BidVol: number
  best3Bid: number
  best3BidVol: number
  best4Bid: number
  best4BidVol: number
  best5Bid: number
  best5BidVol: number
  best6Bid: number
  best6BidVol: number
  best7Bid: number
  best7BidVol: number
  best8Bid: number
  best8BidVol: number
  best9Bid: number
  best9BidVol: number
  best10Bid: number
  best10BidVol: number
  best1Offer: number
  best1OfferVol: number
  best2Offer: number
  best2OfferVol: number
  best3Offer: number
  best3OfferVol: number
  best4Offer: number
  best4OfferVol: number
  best5Offer: number
  best5OfferVol: number
  best6Offer: number
  best6OfferVol: number
  best7Offer: number
  best7OfferVol: number
  best8Offer: number
  best8OfferVol: number
  best9Offer: number
  best9OfferVol: number
  best10Offer: number
  best10OfferVol: number
  buyForeignQtty: number
  buyForeignValue: number
  sellForeignQtty: number
  sellForeignValue: number
  caStatus: string
  tradingStatus: string
  remainForeignQtty: number
  currentBidQty: number
  currentOfferQty: number
  session: string
  tradingUnit: string
  __typename: string
}

Kết quả đạt được vẫn như mong đợi.

shin14 · 22/7/23

NguyenDang95 đã nói:
Xin chào mọi người. Trong bài viết này mình xin giới thiệu đến mọi người cách thức lấy dữ liệu từ trang web bất kỳ. Trong Excel, người dùng có thể sử dụng công cụ có sẵn Power Query để lấy dữ liệu từ một trang web bất kỳ, dữ liệu trả về thông thường là HTML (ở dạng bảng), XML hoặc JSON. Tuy vậy người dùng lại không thể tinh chỉnh những loại dữ liệu nào muốn nhận hoặc chỉ muốn dữ liệu theo nhiều tiêu chí khác nhau (tham số truy vấn (query parameters) của URL hoặc Request Body), lúc này VBA sẽ là lựa chọn hoàn hảo.
Chúng ta xét một ví dụ thực tế dưới đây.
Ví dụ: Lấy dữ liệu chứng khoán theo danh mục từ trang web iboard.ssi.com.vn:
Giả sử người dùng muốn lấy dữ liệu từ trang web iboard.ssi.com với những danh mục như VN100,VN30,VNCONS:
Xem tệp đính kèm 1970426
Để tìm được nguồn cung cấp dữ liệu cho bảng trên, chúng ta thực hiện những bước sau:

Từ trình duyệt web nhân Chromium bất kỳ (Edge, Chrome, Opera, v.v.), người dù nhấn phím F12 để mở Developer Tools

Xem tệp đính kèm 1970430

Tiếp theo, chọn tab Network - Fetch/XHR, từ đây người dùng có thể tìm được nguồn dữ liệu trên bắt nguồn từ API nào.

Dựa vào thông tin của các tab Headers, Payload và Response, người dùng tiến hành viết macro VBA tạo yêu cầu HTTP (HTTP Request) và xử lý phản hồi (Response) nhận được để trình bày dữ liệu ra Excel

Xem tệp đính kèm 1970442

Kết quả:
Xem tệp đính kèm 1970445
Người dùng có thể tìm thấy tệp Excel chứa macro VBA đính kèm theo bài viết này.

Thjm viết hay sưu tầm?

NguyenDang95 · 23/7/23

shin14 đã nói:
Thjm viết hay sưu tầm?

Bài này do mình viết nhé, dựa vào kiến thức và trải nghiệm.

newdayvn · 23/7/23

NguyenDang95 đã nói:
Bài này do mình viết nhé, dựa vào kiến thức và trải nghiệm.

có lấy dữ liệu được ở chỗ không cho sao chép về không thím ?
Em muốn lấy cái khớp lệnh (như hình) (nguồn https://fwt.fialda.com/co-phieu/HSG/giaodich)
mà không lấy về được hết

NguyenDang95 · 23/7/23

newdayvn đã nói:
có lấy dữ liệu được ở chỗ không cho sao chép về không thím ?
Em muốn lấy cái khớp lệnh (như hình) (nguồn https://fwt.fialda.com/co-phieu/HSG/giaodich)
mà không lấy về được hết

Dữ liệu trên được lấy từ API này nhé thím:

hoanganh.pro · 24/7/23

NguyenDang95 đã nói:
Ví dụ: Lấy dữ liệu chứng khoán theo danh mục từ trang web iboard.ssi.com.vn:
Giả sử người dùng muốn lấy dữ liệu từ trang web iboard.ssi.com với những danh mục như VN100,VN30,VNCONS:

Bạn ơi, mình muốn lấy dữ liệu của toàn sàn HOSE, (hoặc 1 mã bất kỳ) thì cần sửa mục nào trong đoạn mã VBA bạn nhỉ! Thanks bạn nhiều!

regnickfree · 24/7/23

thời mới chơi cũng hay phân tích kỹ thuật, thời đó có dùng bọn amibroker hình như nó đã có hỗ trợ lấy dữ liệu realtime rồi

hoanganh.pro · 24/7/23

hay quá chỉ cần điền Hose vào ô B2 là được! Quá ngon!

hoanganh.pro đã nói:
Bạn ơi, mình muốn lấy dữ liệu của toàn sàn HOSE, (hoặc 1 mã bất kỳ) thì cần sửa mục nào trong đoạn mã VBA bạn nhỉ! Thanks bạn nhiều!

meosurry · 26/7/23

NguyenDang95 đã nói:
Bài này do mình viết nhé, dựa vào kiến thức và trải nghiệm.

Có lấy được dữ liệu từ các trang mà nó tính phí cho mục đó ko bạn, ví dụ có mục thì free, nhưng có mục thì phải đăng ký mới xem được, thì có lấy được không? Cảm ơn bạn

NguyenDang95 · 26/7/23

meosurry đã nói:
Có lấy được dữ liệu từ các trang mà nó tính phí cho mục đó ko bạn, ví dụ có mục thì free, nhưng có mục thì phải đăng ký mới xem được, thì có lấy được không? Cảm ơn bạn

Nếu là mục tính phí thì chắc là phải đăng nhập tài khoản bạn nhỉ?

DuySGN · 27/7/23

NguyenDang95 đã nói:
Xin chào mọi người. Trong bài viết này mình xin giới thiệu đến mọi người cách thức lấy dữ liệu từ trang web bất kỳ. Trong Excel, người dùng có thể sử dụng công cụ có sẵn Power Query để lấy dữ liệu từ một trang web bất kỳ, dữ liệu trả về thông thường là HTML (ở dạng bảng), XML hoặc JSON. Tuy vậy người dùng lại không thể tinh chỉnh những loại dữ liệu nào muốn nhận hoặc chỉ muốn dữ liệu theo nhiều tiêu chí khác nhau (tham số truy vấn (query parameters) của URL hoặc Request Body), lúc này VBA sẽ là lựa chọn hoàn hảo.
Chúng ta xét một ví dụ thực tế dưới đây.
Ví dụ: Lấy dữ liệu chứng khoán theo danh mục từ trang web iboard.ssi.com.vn:
Giả sử người dùng muốn lấy dữ liệu từ trang web iboard.ssi.com với những danh mục như VN100,VN30,VNCONS:
Xem tệp đính kèm 1970426
Để tìm được nguồn cung cấp dữ liệu cho bảng trên, chúng ta thực hiện những bước sau:

Từ trình duyệt web nhân Chromium bất kỳ (Edge, Chrome, Opera, v.v.), người dù nhấn phím F12 để mở Developer Tools

Xem tệp đính kèm 1970430

Tiếp theo, chọn tab Network - Fetch/XHR, từ đây người dùng có thể tìm được nguồn dữ liệu trên bắt nguồn từ API nào.

Dựa vào thông tin của các tab Headers, Payload và Response, người dùng tiến hành viết macro VBA tạo yêu cầu HTTP (HTTP Request) và xử lý phản hồi (Response) nhận được để trình bày dữ liệu ra Excel

Xem tệp đính kèm 1970442

Kết quả:
Xem tệp đính kèm 1970445
Người dùng có thể tìm thấy tệp Excel chứa macro VBA đính kèm theo bài viết này.

thớt có thể hướng dẫn chi tiết từ đoạn này được không ?
cv của mình bên operation , thường hay phải lấy dữ liệu từ 1 bên thứ 3 về rồi match công thức để ra 1 bản report hoàn chỉnh .
thớt có cách này lấy dữ liệu kiểu tự động API không ?

NguyenDang95 · 27/7/23

DuySGN đã nói:
thớt có thể hướng dẫn chi tiết từ đoạn này được không ?
cv của mình bên operation , thường hay phải lấy dữ liệu từ 1 bên thứ 3 về rồi match công thức để ra 1 bản report hoàn chỉnh .
thớt có cách này lấy dữ liệu kiểu tự động API không ?

Bạn cần lấy dữ liệu từ API cụ thể nào? Bởi vì cấu trúc dữ liệu trả về mỗi API mỗi khác nên mình cũng không thể hướng dẫn hết tất cả các trường hợp được.

hoanganh.pro · 28/7/23

@NguyenDang95

NguyenDang95 đã nói:
Bạn cần lấy dữ liệu từ API cụ thể nào? Bởi vì cấu trúc dữ liệu trả về mỗi API mỗi khác nên mình cũng không thể hướng dẫn hết tất cả các trường hợp được.

Bạn có thể giúp mình lấy dữ liệu API trong trường hợp này không ạ:
Mình có trang web: thegioididong.com/tien-ich/nap-tien-dien-thoai-tra-sau

giờ muốn lấy DATA về file excel như sau:

Thì các bước cần làm như nào ạ!
Cảm ơn bạn, rất mong nhận được hướng dẫn chi tiết từ bạn!

DuySGN · 28/7/23

hoanganh.pro đã nói:
@NguyenDang95

Bạn có thể giúp mình lấy dữ liệu API trong trường hợp này không ạ:
Mình có trang web: thegioididong.com/tien-ich/nap-tien-dien-thoai-tra-sau
Xem tệp đính kèm 1980642
giờ muốn lấy DATA về file excel như sau:
Xem tệp đính kèm 1980645

Thì các bước cần làm như nào ạ!
Cảm ơn bạn, rất mong nhận được hướng dẫn chi tiết từ bạn!

case này khó đấy . mấy cái trang này cần phải có API key mới lấy dữ liệu về được .

NguyenDang95 · 28/7/23

hoanganh.pro đã nói:
@NguyenDang95

Bạn có thể giúp mình lấy dữ liệu API trong trường hợp này không ạ:
Mình có trang web: thegioididong.com/tien-ich/nap-tien-dien-thoai-tra-sau
Xem tệp đính kèm 1980642
giờ muốn lấy DATA về file excel như sau:
Xem tệp đính kèm 1980645

Thì các bước cần làm như nào ạ!
Cảm ơn bạn, rất mong nhận được hướng dẫn chi tiết từ bạn!

Để mình ngâm cứu thử xem sao nhé.

newdayvn · 28/7/23

NguyenDang95 đã nói:
Dữ liệu trên được lấy từ API này nhé thím:
Xem tệp đính kèm 1971459
Xem tệp đính kèm 1971460

Em mò không ra, thím hỗ trợ em cái file lấy cái đó được ko ? em xin cảm ơn và hậu tạ

NguyenDang95 · 29/7/23

hoanganh.pro đã nói:
@NguyenDang95

Bạn có thể giúp mình lấy dữ liệu API trong trường hợp này không ạ:
Mình có trang web: thegioididong.com/tien-ich/nap-tien-dien-thoai-tra-sau
Xem tệp đính kèm 1980642
giờ muốn lấy DATA về file excel như sau:
Xem tệp đính kèm 1980645

Thì các bước cần làm như nào ạ!
Cảm ơn bạn, rất mong nhận được hướng dẫn chi tiết từ bạn!

Mình tìm thấy API này:

Tuy nhiên, trang web này dùng captcha để chặn bot lấy dữ liệu (tức là trong trường hợp này dùng macro VBA gửi yêu cầu đến API này), buộc phải có sự tương tác của người dùng để giải captcha, nên mình bó tay rồi.

NguyenDang95 · 29/7/23

newdayvn đã nói:
Em mò không ra, thím hỗ trợ em cái file lấy cái đó được ko ? em xin cảm ơn và hậu tạ

Gửi thím.
Dữ liệu được lấy bằng Power Query.

hoanganh.pro · 29/7/23

NguyenDang95 đã nói:
Tuy nhiên, trang web này dùng captcha để chặn bot lấy dữ liệu (tức là trong trường hợp này dùng macro VBA gửi yêu cầu đến API này), buộc phải có sự tương tác của người dùng để giải captcha, nên mình bó tay rồi.

Thanks bạn nhiều. Cho mình hỏi thêm 1 chút là mình có vào trang https://liveboard.cafef.vn/ để tìm URL cho Power Query tuy nhiên có vẻ trang họ ẩn đi thì phải, vậy có cách nào để tìm ra URL chuẩn không ạ?
Mình đã thử URL: https://msh-data.cafef.vn/graphql vào mà không được.
Và mò trên YTB có URL này: https://banggia.cafef.vn/stockhandler.ashx?center=undefined cho sàn HOSE thì ok mà tìm trong mục Network này không ra.
Cảm ơn bạn!

newdayvn đã nói:
Em mò không ra, thím hỗ trợ em cái file lấy cái đó được ko ? em xin cảm ơn và hậu tạ

Mình có làm video theo hướng dẫn của chủ thớt, bạn xem cho dễ hiểu sau có thể áp dụng cho các mã chứng khoán khác nhé:

kiến thức [Excel VBA] Lấy dữ liệu từ trang web bất kỳ

Senior Member

Tệp đính kèm

Senior Member

Member

Senior Member

Đã tốn tiền

Tệp đính kèm

Senior Member

Senior Member

Senior Member

Senior Member

Junior Member

Senior Member

Junior Member

Senior Member

Senior Member

Junior Member

Senior Member

Đã tốn tiền

Senior Member

Senior Member

Tệp đính kèm

Senior Member

Chủ đề tương tự

Thống kê chủ đề

Chia sẻ trang này