دریافت اطلاعات از سایت

استخراج داده از وبسایت یکی از مسائل پرکاربرد به شمار میاد. خیلی وقت ها پیش میاد که بخوایم داده های موجود در یک سایت رو در یک فایل اکسل داشته باشیم و بدون اینکه نیاز به باز کردن سایت باشه، داده های اپدیت شده رو در اکسل ببینیم و بررسی های لازم رو روش انجام بدیم. مثلا فرض کنید قیمت ارز، طلا و سایر شاخص های مالی رو در بازه های زمانی خاص لازم داریم و میخوایم این داده ها رو در اکسل داشته باشیم که بتونیم محاسباتی رو روی آنها اعمال کنیم. برای همین امروزه دریافت اطلاعات از سایت در اکسل مسئله مهمی به حساب میاد.

اکسل با توجه به ساختار سایت مورد نظر، تا حدی میتونه برخی اطلاعات رو برای ما فراخوانی و بروزرسانی بکنه.

البته به این نکته باید دقت داشته باشیم که برای داشتن اطلاعات یک سایت، بسته به ساختار مورد نظر و نوع اطلاعات درخواستی روشهای مختلفی برای جمع آوری داده ها وجود داره. حتی نرم افزارها و سایت هایی برای این کار طراحی شده اند که در سطوح و جزئیات مختلف میتونن این کار رو (رایگان و غیررایگان) انجام بدن.

اکسل چطور میتونه داده ها رو از سایت فراخوانی کنه؟

اکسل از ورژن های قدیمی مثل ۲۰۰۳ این امکان رو داشته که بتونه اطلاعات سایتی رو فراخوانی و بروزرسانی کنه. خیلی از افراد حتی به این فکر نمیکنند که بتونن داده های یک سایت رو داشته باشن چه برسه به اینکه اکسل هم بتونه این کار رو انجام بده. اما انجام این کار در اکسل خیلی راحت و کاربردی انجام میشه. برای اینکه ببینیم چطور میتونیم این کار رو انجام بدیم، مراحل زیر رو انجام میدیم:

آدرس سایت مورد نظر رو پیدا کنید

سایتی که انتخاب کردم که داده ها رو از روی اون فراخوانی کنیم سایت www.tgju.org هست. این سایت اطلاعات مربوط به قیمت طلا، ارز، شاخص بورس و … رو بصورت آنلاین در اختیار ما قرار میده و ما میتونیم این اطلاعات ور در اکسل داشته باشیم.

اکسل رو باز کنید و داده ها رو فراخوانی کنید

فایل اکسل رو باز کرده و از تب Data و قسمت Get & Transform Data گزینه From Web رو انتخاب میکنیم.

شکل ۱ – استخراج داده از وبسایت- From web

در پنجره نمایش داده شده، آدرس سایت مورد نظر رو وارد کرده و Ok رو میزنیم. در این قسمت دو گزینه basic و advance داریم که در گزینه advance به ما امکاناتی رو میده که چچطور داده ها جمع آوری بشه. اما در این تمرین، گزینه Basic جواب نیاز ما رو میده.

شکل ۲ – استخراج داده از وبسیات – آدرس سایت مورد نظر

بعد از زدن Ok پنجره ای باز میشه که همه جداول موجود در اون صفحه سایت که قابل وارد کردن به اکسل هستن رو نمایش میده.

شکل ۳ – دریافت اطلاعات از سایت – لیست جداول موجود در صفحه وب مورد نظر

حالا از پنجره نمایش داده شده، جداول مورد نظر رو تیک میزنیم. مثلا د راینجا جدول مربوط به نرخ ارز، طلا و ارز مجازی رو انتخاب میکنیم.

برای اینکه بتونیم چند جدول رو انتخاب کنیم تیک گزینه Select Multiple items رو میزنیم. در اینجا با توجه به بررسی های انجام شده، میدونیم که جدول شماره ۱۶، ارز مجازی، جدول شماره ۲، نرخ طلا و جدول شماره ۴، نرخ سکه رو نشون میده.

شکل ۴ – استخراج داده از وبسایت – انتخاب جداول داد های مورد نظر

حالا کافیه جداول مورد نظر رو به اکسل وارد کنیم. برای این کار روی گزینه Load کلیک میکنیم. با کلیک بر روی گزینه Load سه جدولی که انتخاب کردیم، در نوار Queries & Connections نمایش داده میهش که با نگه داشتن موس روی هر کدوم، محتویات جدول، آخرین زمان بروز رسانی و … نمایش داده میشه که با کلیک روی … و انتخاب گزینه Load to میتونیم جدول مورد نظر رو وارد شیت اکسل کنیم.

شکل ۵ – استخراج داده از وبسایت- جداول اضافه شده در قسمت Queries & Connections

با کلیک روی Load to پنجره ای باز میشه که میپرسه این داده ها در چه قالبی وارد اکسل بشن؟ گزینه Table رو انتخاب میکنیم و سلول مورد نظر رو برای ورود داده ها انتخاب میکنیم:

شکل ۶ – استخراج داده از وبسایت – ورود داده ها به شیت اکسل

با زدن Ok داده های جدول انتخاب شده وارد اکسل خواهد شد. همین کار رو برای دو جدول دیگه هم انجام میدیم و هر سه جدول رو در شیت اکسل وارد میکنیم. (با توجه به جزئیات موجود، میتونیم هر جدول رو در شیت های جداگانه (New Worksheet) هم وارد کنیم)

شکل ۷- استخراج داده از وبسایت – وارد کردن داده ها در شیت اکسل

بروزرسانی داده ها بعد از دریافت اطلاعات از سایت

خب سوالی که پیش میاد اینه که بروز رسانی این داده به چه صورت هست؟

مثلا اگه بخوایم در بازه های زمانی مشخص، این داده ها بروز رسانی بشه باید چکار کنیم؟ بروز رسانی بصورت دستی (زمان های نامنظم) چطور انجام میشه؟

بروز رسانی کوئری ها بصورت دستی

برای انجام تنظیمات مربوط به بروزرسانی باید طبق زیر عمل کنیم:

اگر بخوایم بروز رسانی دستی و غیرخودکار انجام بشه، هر بار که نیاز به بروز رسانی بود، باید گزینه Refresh از تب Table Tools رو بزنیم:

شکل ۸ – استخراج داده از وبسایت – بروزرسانی بصورت دستی

با زدن refresh all همه کوئری ها بروز رسانی میشن. اگر بخوایم هر کوئری جداگانه آپدیت بشه، کوئری رو انتخاب کرده و از تب Query Tools گزینه Refresh رو انتخاب میکنیم. یا در گوشه سمت راست هر کوئری روی گزینه Refresh کلیک میکنیم. شکل ۹

شکل ۹ – دریافت اطلاعات از سایت – بروز رسانی کوئری

بروز رسانی کوئری ها بصورت خودکار

برای هر کوئری میتونیم تعیین کنیم که چطور و در چه فواصل زمانی بروزرسانی بشه. برای این کار کافیه روی جدول مورد نظر کلیک کرده و از تب Table Tools و گزینه Refresh روی گزینه Connection Properties کلیک کنیم و تنظیمات دلخواه رو در پنجره نمایش داده شده انجام بدیم. همونطور که در شکل ۱۰ نمایش داده شده، فاصله زمانی بروزرسانی رو میتونیم تنظیم کنیم. با زدن تیک Refresh data when opening the file با هر بار باز کردن فایل، داده ها بروز رسانی میشه. توجه داشته باشید که همه این موارد در صورتی قابل انجام هست که اتصال به اینترنت برقرار باشه.

شکل ۱۰ – استخراج داده ها از وبسایت – تنظیم زمان های بروزرسانی خودکار

این تنظیمات رو از مسیر شکل ۱۱ نیز میتونیم انجام بدیم. با نگه داشتن روی هر کوئری و زدن … با کلیک روی گزینه Properties  پنجره تنظیم زمان بروزرسانی نمایش داده میشه.

شکل ۱۱- استخراج داده از وبسایت – انجام تنظیمات زمان های بروزرسانی از روی هر کوئری

حالا که داده های مورد نظر سایت رو در فایل اکسل داریم با استفاده از توابع و ابزارهای گزارشگیری و تحلیل داده میتونیم تحلیل های مورد نظر رو روی داده ها انجام بدیم. ابزار Power Query وسیله بسیار خوبی برای دریافت اطلاعات از سایت های مختلف هست که این کار یکی از توانایی هاشه.

یکی از مسائلی که خیلی مهمه و مورد نیاز خیلی افراد هست اینه که هر بار قبل از رفرش کردن و بروزرسانی داده ها، داده های قبلی در جای دیگری ذخیره بشه و بعد بروز رسانی انجام بشه. این کار کمک میکنه که بتونیم تحلیل های بهتری روی داده ها داشته باشیم.

برای این کار باید کد VBA بنویسیم که هر بار با اجرا کردنش، داده های فعلی رو در ادامه دیتابیسی ذخیره بکنه و بعد عملیات بروزرسانی انجام بشه. که نمونه کد رو در فایل زیر می بینید.

دانلود فایل نمونه این آموزش

برای دانلود این فایل روی دکمه زیر کلیک کنید:

با عضویت در سایت به صورت مستقیم دانلود کنید

• ارسال به ایمیل

Des