سلام دوستان عزیز وقتتون بخیر؛ آرزوی اوقات خوشی رو براتون داریم 🙂 در این جلسه از سری آموزش های پویش پایدار در خدمتتون هستیم برای آموزش نحوه استخراج و انتقال اطلاعات از یک وبسایت به اکسل.
آموزش دریافت و انتقال اطلاعات از یک سایت به اکسل
زمانیکه میخواهیم یک داشبورد حرفه ای در اکسل طراحی کنیم، ممکن است نیازمند دریافت و انتقال اطلاعات از یک سایت به اکسل باشیم. برای مثال اگر بخواهیم داشبوردی مرتب با پورتفولیوی بورسی خودمان ایجاد کنیم، نیازمند دریافت اطلاعات لحظه ای بورس در اکسل هستیم. یا اگر قصد طراحی داشبوردی برای محاسبات قیمت طلا و دلار را داشته باشیم، نیازمند دریافت اطلاعات از سایت اطلاع رسانی طلا و ارز (TGJU) هستیم.
برای دریافت و انتقال اطلاعات از یک سایت به اکسل راهکارهای متفاوتی وجود دارد که در زیر به صورت موردی به آن ها اشاره شده است:
- وب اسکرپینگ با VBA (کدنویسی در اکسل)
- استخراج داده های وبسایت با استفاده از Power Query در اکسل
- استفاده از API وبسایت در VBA
در اکثر سایت های ایرانی و خارجی تنها به مورد دوم (استفاده از پاورکوئری) اشاره شده است. این روش در بسیاری اوقات کارآمد نیست و انعطاف پذیری بالایی ندارد. برای مثال زمانیکه بخواهیم یک المان خاص را از یک صفحه وبسایت به یک سلول خاص اکسل منتقل کنیم، ممکن است امکان پذیر نباشد. فرضا در سایت TGJU میخواهیم ساعت لحظه ای سایت را که در تصویر زیر با بیضی قرمز رنگ مشخص شده است، در سلول E2 اکسل داشته باشیم.
نمای سایت TGJU (اطلاع رسانی طلا و ارز)
اگر از پاورکوئری برای این کار استفاده کنیم، خروجی آن به صورت تصویر زیر است. همانطورکه ملاحظه میکنید، ساعت و تاریخ مورد نظر به صورت جدا جدا در سلول های F2 تا F4 استخراج شده اند که مطلوب ما نیست و برای انتقال آن به سلول E2 یا باید از فرمول نویسی اکسل استفاده کرد و یا از VBA. لذا ترجیح ما برای چنین مواردی، استفاده از کدنویسی VBA است.
اطلاعات منتقل شده از سایت tgju به اکسل توسط پاورکوئری
استخراج و انتقال اطلاعات از وبسایت به اکسل با استفاده از کدنویسی VBA
برای دریافت اطلاعات از سایت TGJU از کد VBA استفاده کرده ایم که کلیت این کد بر مبنای جستجوی قیمت ها با استفاده از تگ های HTML سایت است. برای مثال قیمت انس طلا در تگ td و بعد از واژه “انس طلا” قرار گرفته است. لذا با استفاده از کدهای VBA به دنبال چنین تگی میگردیم تا قیمت انس طلا از سایت استخراج شود. البته میتوان از طریق استفاده از API این وبسایت نیز کدنویسی VBA را انجام داد اما نیازمند خرید اشتراک API این سایت است.
در ادامه به تکه‌های مختلف این کد می‌پردازیم:
تعریف متغیرها:
در این قسمت از کد، متغیرهای مختلفی برای ذخیره اطلاعات از جمله آبجکت‌های HTTP و HTML، آدرس URL، و مقادیر قیمت‌ها تعریف می‌شوند.
Dim html As Object
Dim url As String
Dim elementsTh As Object
Dim elementsTd As Object
Dim element As Object
Dim tdElement As Object
Dim priceDollar As String
Dim priceGoldOunce As String
Dim PriceGold18 As String
Dim priceEmami As String
Dim priceBaharAzadi As String
Dim priceNimSkeeh As String
Dim priceRobSekkeh As String
Dim priceEuro As String
Dim searchDollar As String
Dim priceGold24 As String
Dim searchGold18 As String
Dim searchEmami As String
Dim searchBaharAzadi As String
Dim searchNimSekkeh As String
Dim searchRobSekkeh As String
Dim searchEuro As String
Dim searchGold24 As String
Dim searchOunce As String
Dim i As Integer
Dim found As Boolean
تنظیم URL:
این خط URL وب‌سایتی را که قرار است از آن داده دریافت شود، مشخص می‌کند.
ایجاد و ارسال درخواست HTTP:
این بخش یک شیء HTTP ایجاد می‌کند، یک درخواست GET به URL ارسال می‌کند، و پاسخ را بدون کشینگ دریافت می‌کند.
http.Open “GET”, url, False
http.setRequestHeader “Cache-Control”, “no-cache”
http.setRequestHeader “Pragma”, “no-cache”
http.Send
پردازش پاسخ HTML:
پس از دریافت پاسخ، محتویات HTML به عنوان یک فایل HTML بارگذاری می‌شوند تا بتوان با آن‌ها کار کرد.
html.body.innerHTML = http.responseText
خواندن داده‌ها از شیت 2:
در شیت 2 اکسلی که در آن کدنویسی میکنیم، املای صحیح مواردی که میخواهیم از سایت استخراج کنیم را به صورت تصویر زیر قرار داده ایم:
عناوین سکه ها و ارزهای موردنظر برای استخراج
کدهای زیر برای خواندن مقادیر از سلول‌های یک شیت خاص و ذخیره آن‌ها در متغیرهای مختلف است.
searchGold24 = ThisWorkbook.Sheets(2).Range(“A2”).Value
searchGold18 = ThisWorkbook.Sheets(2).Range(“A3”).Value
searchDollar = ThisWorkbook.Sheets(2).Range(“A4”).Value
searchEuro = ThisWorkbook.Sheets(2).Range(“A5”).Value
searchEmami = ThisWorkbook.Sheets(2).Range(“A6”).Value
searchBaharAzadi = ThisWorkbook.Sheets(2).Range(“A7”).Value
searchNimSekkeh = ThisWorkbook.Sheets(2).Range(“A8”).Value
searchRobSekkeh = ThisWorkbook.Sheets(2).Range(“A9”).Value
در این قسمت، مقادیر مربوط به نام دارایی‌ها از سلول‌های A1 تا A9 در شیت 2 استخراج می‌شود تا در ادامه برای مقایسه با محتوای وبسایت استفاده شود.
پیدا کردن و استخراج قیمت دلار:
در این قسمت کد مربوط به استخراج قیمت دلار از سایت tgju را مشاهده میکنیم. قیمت دلار در کدهای html این سایت در کلاسی به نام market-price قرار گرفته است.
Set element = elementsTh.Item(i)
If InStr(element.innerText, searchDollar) > 0 Then
priceDollar = element.ParentNode.getElementsByClassName(“market-price”)(0).innerText
Exit For
End If
Next i
در این بخش، برنامه در میان المان‌های th در HTML به دنبال عبارت دلار میگردد و اگر آن را پیدا کند، قیمت آن را استخراج می‌کند.
پیدا کردن و استخراج قیمت انس طلا:
در این قسمت کد مربوط به استخراج قیمت انس طلا از سایت tgju را مشاهده میکنیم. در کد زیر، در تگ های td سایت، به دنبال واژه “انس طلا” میگردد. چراکه طبق جستجویی که در تگ های html سایت tgju انجام دادیم، قیمت انس طلا در تگ td در زیر واژه “انس طلا” قرار گرفته است.
Set element = elementsTh.Item(i)
If InStr(element.innerText, searchOunce) > 0 Then
priceGoldOunce = element.ParentNode.getElementsByTagName(“td”)(0).innerText
Exit For
End If
Next i
مشابه با مرحله قبل، در اینجا نیز قیمت انس طلا از HTML استخراج می‌شود.
پیدا کردن و استخراج قیمت‌های دیگر (طلا 18 عیار، سکه امامی، بهار آزادی، نیم‌سکه، ربع‌سکه، یورو، و طلا 24 عیار): برای هر کدام از این موارد، یک حلقه مشابه اجرا می‌شود تا المان مورد نظر در HTML پیدا و قیمت آن استخراج شود.
نمایش قیمت‌ها در شیت 1:
با استفاده از کد زیر، قیمت انس طلا را در سلول B2 در شیت 1 اکسل درج می کنیم.
ThisWorkbook.Sheets(1).Range(“B2”).Value = priceGoldOunce
Else
MsgBox “قیمت دلار یافت نشد. لطفاً آدرس یو آر ال یا موقعیت کلاس سی اس اس را بررسی کنید.”
End If
در نهایت با ورود تمام این کدها در یک ماژول در قسمت VBA اکسل، خروجی فایل به صورت زیر خواهد شد.
خروجی نهایی داشبورد استخراج و محاسبات قیمت طلا، سکه و ارز
لازم به ذکر است که داشبوردی که تصویر فوق مشاهده می کنید شامل کدهای VBA بیشتری نسبت به کدهای ارائه شده در این پست می باشد که مربوط به ایجاد محیط گرافیکی و محاسباتی است. در این پست فقط نحوه استخراج داده های مورد نظر از سایت اطلاع رسانی طلا و ارز توضیح داده شد. برای دسترسی به این فایل می توانید روی لینک داشبورد محاسبه قیمت طلا، سکه و ارز کلیک کنید.
ممنون از توجه شما 🙂
هر گونه سوال، پیشنهاد یا انتقادی در رابطه با دوره رایگان آموزش اکسل دارید در بخش دیدگاه‌ها مطرح نمایید تا بتوانیم بهترین آموزش ها را برای شما علاقمندان به آموزش اکسل تهیه کنیم.
