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

NguyenDang95

NguyenDang95

Senior Member
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:
1690031699633.png

Để 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:
  1. 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
  2. 1690031901957.png
  3. 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.
  4. 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
  5. 1690032117080.png
Kết quả:
1690032216617.png

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.
1715567215481.png

1715567202309.png
 

Attachments

  • SSI.zip
    2 MB · Views: 11
Last edited:
(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.
1690032691286.png
 
NguyenDang95 said:
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:
View attachment 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:
  1. 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
  2. View attachment 1970430
  3. 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.
  4. 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
  5. View attachment 1970442
Kết quả:
View attachment 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.
Click to expand...
Thjm viết hay sưu tầm?
 
NguyenDang95 said:
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:
Click to expand...
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!
 
NguyenDang95 said:
Bài này do mình viết nhé, dựa vào kiến thức và trải nghiệm.
Click to expand...
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
 
meosurry said:
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
Click to expand...
Nếu là mục tính phí thì chắc là phải đăng nhập tài khoản bạn nhỉ?
 
NguyenDang95 said:
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:
View attachment 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:
  1. 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
  2. View attachment 1970430
  3. 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.
  4. 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
  5. View attachment 1970442
Kết quả:
View attachment 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.
Click to expand...
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 ?
 
DuySGN said:
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 ?
Click to expand...
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.
 
@NguyenDang95
NguyenDang95 said:
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.
Click to expand...
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
1690511432198.png

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


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!
 
hoanganh.pro said:
@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
View attachment 1980642
giờ muốn lấy DATA về file excel như sau:
View attachment 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!
Click to expand...
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 .
 
hoanganh.pro said:
@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
View attachment 1980642
giờ muốn lấy DATA về file excel như sau:
View attachment 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!
Click to expand...
Để mình ngâm cứu thử xem sao nhé.
 
hoanganh.pro said:
@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
View attachment 1980642
giờ muốn lấy DATA về file excel như sau:
View attachment 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!
Click to expand...
Mình tìm thấy API này:
1690594962385.png
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.
1690595124460.png
 
NguyenDang95 said:
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.
Click to expand...
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!
1690597559239.png




newdayvn said:
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ạ
Click to expand...
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é:
 

Similar threads

NguyenDang95
kiến thức [Excel VBA] Xử lý bất đồng bộ khi làm việc với cơ sở dữ liệu
Replies
0
Views
268
NguyenDang95
NguyenDang95
H
thắc mắc Ổ cứng để lưu trữ dữ liệu quan trọng dài hạn
Replies
9
Views
390
ceibga
ceibga
NguyenDang95
thảo luận Giới thiệu ngôn ngữ công thức M của Power Query trong Excel và Power BI
Replies
0
Views
275
NguyenDang95
NguyenDang95
bingo_
thắc mắc Python psycopg2.connect query database không có dữ liệu
Replies
2
Views
179
_noat_
_noat_
M
tin tức Firefox 126 đã ra mắt: Bảo mật hơn, ít bị theo dõi hơn và Facebook nhanh hơn
2 3
Replies
53
Views
4K
Mainboard o_o
M

Share this page

Back
Top