এক্সেল এ আপডেট করা বিনিময় হার

আমি পরবর্তী স্বয়ংক্রিয় আপডেটের সাথে ইন্টারনেট থেকে এক্সেলে ডেটা আমদানি করার উপায়গুলি বারবার বিশ্লেষণ করেছি। নির্দিষ্টভাবে:

• এক্সেল 2007-2013 এর পুরানো সংস্করণগুলিতে, এটি সরাসরি ওয়েব অনুরোধের সাথে করা যেতে পারে।
• 2010 থেকে শুরু করে, এটি পাওয়ার কোয়েরি অ্যাড-ইন দিয়ে খুব সুবিধাজনকভাবে করা যেতে পারে।
  

মাইক্রোসফ্ট এক্সেলের সর্বশেষ সংস্করণগুলিতে এই পদ্ধতিগুলিতে, আপনি এখন আরেকটি যোগ করতে পারেন - অন্তর্নির্মিত ফাংশনগুলি ব্যবহার করে XML ফর্ম্যাটে ইন্টারনেট থেকে ডেটা আমদানি করা।

XML (এক্সটেনসিবল মার্কআপ ল্যাঙ্গুয়েজ = এক্সটেনসিবল মার্কআপ ল্যাঙ্গুয়েজ) একটি সার্বজনীন ভাষা যা যেকোনো ধরনের ডেটা বর্ণনা করার জন্য ডিজাইন করা হয়েছে। প্রকৃতপক্ষে, এটি সাধারণ পাঠ্য, তবে ডেটা কাঠামো চিহ্নিত করার জন্য এটিতে বিশেষ ট্যাগ যুক্ত করা হয়েছে। অনেক সাইট তাদের ডাটা বিনামূল্যে XML ফরম্যাটে যে কেউ ডাউনলোড করার জন্য প্রদান করে। আমাদের দেশের কেন্দ্রীয় ব্যাংকের ওয়েবসাইটে (www.cbr.ru), বিশেষত, অনুরূপ প্রযুক্তির সাহায্যে, বিভিন্ন মুদ্রার বিনিময় হারের ডেটা দেওয়া হয়। মস্কো এক্সচেঞ্জ ওয়েবসাইট (www.moex.com) থেকে আপনি একইভাবে স্টক, বন্ড এবং অন্যান্য অনেক দরকারী তথ্যের জন্য উদ্ধৃতি ডাউনলোড করতে পারেন।

সংস্করণ 2013 থেকে, এক্সেলের কার্যপত্রক কোষগুলিতে ইন্টারনেট থেকে সরাসরি XML ডেটা লোড করার জন্য দুটি ফাংশন রয়েছে: একটি বন্ধু পূর্ণ নাম লিখুন (একটি বন্ধু পূর্ণ নাম লিখুন) и FILTER.XML (ফিল্টারএক্সএমএল). তারা জোড়ায় কাজ করে - প্রথমে ফাংশন একটি বন্ধু পূর্ণ নাম লিখুন পছন্দসই সাইটে একটি অনুরোধ নির্বাহ করে এবং XML বিন্যাসে তার প্রতিক্রিয়া প্রদান করে, এবং তারপর ফাংশন ব্যবহার করে FILTER.XML আমরা এই উত্তরটিকে উপাদানগুলিতে "পার্স" করি, এটি থেকে আমাদের প্রয়োজনীয় ডেটা বের করে।

আসুন একটি ক্লাসিক উদাহরণ ব্যবহার করে এই ফাংশনগুলির কার্যকারিতা দেখি - আমাদের দেশের সেন্ট্রাল ব্যাংকের ওয়েবসাইট থেকে একটি নির্দিষ্ট তারিখের ব্যবধানের জন্য যে কোনো মুদ্রার বিনিময় হার আমদানি করা। আমরা একটি ফাঁকা হিসাবে নিম্নলিখিত নির্মাণ ব্যবহার করব:

এখানে:

• হলুদ কক্ষগুলিতে আমাদের আগ্রহের সময়কালের শুরু এবং শেষ তারিখ থাকে।
• নীল একটি কমান্ড ব্যবহার করে মুদ্রার একটি ড্রপ-ডাউন তালিকা আছে ডেটা - বৈধতা - তালিকা (তথ্য — বৈধতা — তালিকা).
• সবুজ কক্ষে, আমরা একটি ক্যোয়ারী স্ট্রিং তৈরি করতে এবং সার্ভারের প্রতিক্রিয়া পেতে আমাদের ফাংশনগুলি ব্যবহার করব।
• ডানদিকের টেবিলটি মুদ্রা কোডগুলির একটি রেফারেন্স (আমাদের একটু পরে এটির প্রয়োজন হবে)।
  

চলো যাই!

ধাপ 1. একটি ক্যোয়ারী স্ট্রিং গঠন

সাইট থেকে প্রয়োজনীয় তথ্য পেতে, আপনাকে সঠিকভাবে জিজ্ঞাসা করতে হবে। আমরা www.cbr.ru এ যাই এবং মূল পৃষ্ঠার ফুটারে লিঙ্কটি খুলি' প্রযুক্তিগত সম্পদ'- XML ব্যবহার করে ডেটা পাওয়া যাচ্ছে (http://cbr.ru/development/SXML/)। আমরা একটু নীচে স্ক্রোল করি এবং দ্বিতীয় উদাহরণে (উদাহরণ 2) আমাদের যা প্রয়োজন তা থাকবে - একটি নির্দিষ্ট তারিখের ব্যবধানের বিনিময় হার পাওয়া:

যেমন আপনি উদাহরণ থেকে দেখতে পাচ্ছেন, ক্যোয়ারী স্ট্রিংটিতে অবশ্যই শুরুর তারিখ থাকতে হবে (date_req1) এবং শেষ (date_req2) আমাদের আগ্রহের সময়কাল এবং মুদ্রা কোড (VAL_NM_RQ), আমরা যে হার পেতে চাই। আপনি নীচের টেবিলে মূল মুদ্রার কোডগুলি খুঁজে পেতে পারেন:

সেন্ট্রাল ব্যাঙ্কের ওয়েবসাইটেও মুদ্রা কোডগুলির একটি সম্পূর্ণ নির্দেশিকা পাওয়া যায় - দেখুন http://cbr.ru/scripts/XML_val.asp?d=0

এখন আমরা একটি পত্রকের একটি ঘরে একটি ক্যোয়ারী স্ট্রিং তৈরি করব:

• টেক্সট কনক্যাটেনেশন অপারেটর (&) এটিকে একসাথে রাখার জন্য;
  
• বৈশিষ্ট্য VPR (ভলুকআপ)ডিরেক্টরিতে আমাদের প্রয়োজনীয় মুদ্রার কোড খুঁজে পেতে;
  
• বৈশিষ্ট্য টেক্সট (পাঠ্য), যা একটি স্ল্যাশের মাধ্যমে প্রদত্ত প্যাটার্ন দিন-মাস-বছর অনুযায়ী তারিখকে রূপান্তর করে।

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&  "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)  

ধাপ 2. অনুরোধটি কার্যকর করুন

এখন আমরা ফাংশন ব্যবহার একটি বন্ধু পূর্ণ নাম লিখুন (একটি বন্ধু পূর্ণ নাম লিখুন) শুধুমাত্র আর্গুমেন্ট হিসাবে জেনারেট করা ক্যোয়ারী স্ট্রিং সহ। উত্তরটি হবে এক্সএমএল কোডের একটি দীর্ঘ লাইন (শব্দের মোড়ক চালু করা এবং যদি আপনি এটিকে সম্পূর্ণরূপে দেখতে চান তবে ঘরের আকার বাড়ানো ভাল):

ধাপ 3. উত্তর পার্সিং

প্রতিক্রিয়া ডেটার গঠন বোঝা সহজ করার জন্য, অনলাইন এক্সএমএল পার্সারগুলির একটি ব্যবহার করা ভাল (উদাহরণস্বরূপ, http://xpather.com/ বা https://jsonformatter.org/xml-parser), যা দৃশ্যত XML কোড ফর্ম্যাট করতে পারে, এতে ইন্ডেন্ট যোগ করে এবং সিনট্যাক্সকে রঙের সাথে হাইলাইট করে। তারপর সবকিছু অনেক পরিষ্কার হয়ে যাবে:

এখন আপনি স্পষ্টভাবে দেখতে পাচ্ছেন যে কোর্সের মানগুলি আমাদের ট্যাগ দ্বারা ফ্রেম করা হয়েছে ..., এবং তারিখগুলি বৈশিষ্ট্য তারিখ ট্যাগে .

তাদের নিষ্কাশন করতে, শীটে খালি কক্ষের দশটি (বা তার বেশি - যদি একটি মার্জিন দিয়ে করা হয়) নির্বাচন করুন (কারণ একটি 10-দিনের তারিখের ব্যবধান সেট করা হয়েছিল) এবং সূত্র বারে ফাংশনটি প্রবেশ করান FILTER.XML (ছাঁকনিএক্সএমএল):

এখানে, প্রথম আর্গুমেন্ট হল একটি সার্ভার রেসপন্স (B8) সহ একটি কক্ষের একটি লিঙ্ক এবং দ্বিতীয়টি হল XPath-এ একটি ক্যোয়ারী স্ট্রিং, একটি বিশেষ ভাষা যা প্রয়োজনীয় XML কোডের টুকরোগুলি অ্যাক্সেস করতে এবং তাদের নিষ্কাশন করতে ব্যবহার করা যেতে পারে। আপনি XPath ভাষা সম্পর্কে আরও পড়তে পারেন, উদাহরণস্বরূপ, এখানে।

এটি গুরুত্বপূর্ণ যে সূত্র প্রবেশ করার পরে, টিপুন না প্রবেশ করান, এবং কীবোর্ড শর্টকাট জন্য ctrl+স্থানপরিবর্তন+প্রবেশ করান, অর্থাৎ এটিকে একটি অ্যারে সূত্র হিসাবে লিখুন (এর চারপাশে কোঁকড়ানো বন্ধনীগুলি স্বয়ংক্রিয়ভাবে যুক্ত হবে)। আপনার যদি এক্সেলের গতিশীল অ্যারেগুলির জন্য সমর্থন সহ Office 365 এর সর্বশেষ সংস্করণ থাকে তবে একটি সাধারণ প্রবেশ করান, এবং আপনাকে আগে থেকে খালি কক্ষ নির্বাচন করতে হবে না – ফাংশনটি নিজেই যতগুলি প্রয়োজন ততগুলি কোষ নেবে৷

তারিখগুলি বের করার জন্য, আমরা একই কাজ করব - আমরা সংলগ্ন কলামে বেশ কয়েকটি খালি ঘর নির্বাচন করব এবং একই ফাংশন ব্যবহার করব, তবে একটি ভিন্ন XPath ক্যোয়ারী সহ, রেকর্ড ট্যাগগুলি থেকে তারিখের বৈশিষ্ট্যগুলির সমস্ত মান পেতে:

=FILTER.XML(B8;"//রেকর্ড/@তারিখ")

এখন ভবিষ্যতে, যখন মূল কক্ষ B2 এবং B3-এ তারিখ পরিবর্তন করা হয় বা সেল B3-এর ড্রপ-ডাউন তালিকায় একটি ভিন্ন মুদ্রা বেছে নেওয়া হয়, তখন আমাদের ক্যোয়ারী স্বয়ংক্রিয়ভাবে আপডেট হবে, নতুন ডেটার জন্য সেন্ট্রাল ব্যাঙ্কের সার্ভারে উল্লেখ করে। ম্যানুয়ালি একটি আপডেট জোর করতে, আপনি অতিরিক্ত কীবোর্ড শর্টকাট ব্যবহার করতে পারেন জন্য ctrl+অল্টার+F9.

• পাওয়ার কোয়েরির মাধ্যমে এক্সেলে বিটকয়েন রেট আমদানি করুন
• এক্সেলের পুরানো সংস্করণে ইন্টারনেট থেকে বিনিময় হার আমদানি করুন