• Scraping Keywords – And – SEO

    Get the Excel with VBA code for Scraping Keywords from Website to help in your SEO tasks.

    Search Engine Optimization (SEO) is the process of making web page content understandable to search robots. Then only, they can realize the purpose of our website and index our pages in a better position. But these days, it has become more of scraping keywords optimization or tweaking to bring websites in the 1st place of SERP (Search Engine Result Page).

    Below topics mainly explains basic SEO tips on how to place your keywords in your content that will improve its meaning to Humans & also to Computers.

    Also Read: Create Keyboard Shortcut - to VBA Macro code - in Excel

    Keyword Scraping Excel – VBA Web Scraping

    In one of my earlier articles, it is explained how to download webpage content with VBA. We are going to re-use that code here in the Keyword scraping Excel App, download content, parse the HTML tags and present the website data analytic.

    1. Open a New Excel workbook.
    2. Press Alt + F11 to view VB editor, Copy paste below code there.
    3. In the first sheet, Cell B1, Type a valid Website URL.
    4. Then, execute the below code by pressing F5 in Excel VBE.

    Note: Free code is available only for link & scripts extracting. To analyze keywords & for good search engine optimization solutions:


    Once start execution, code fetch data from website using the “msxml2.xmlhttp” functionality and put the content in ‘htmlfile’ object. With the function ‘getElementsByTagName’ the HTML tags can be parsed and the data embedded in those tags can be extracted. Similarly, the above code will also fetch “href” links to other web pages and scripts running in that page.

    HTML Tags – Search Engine Optimization(SEO) Solutions

    Though Computers can understand only keywords, Experts in Google, BING and other Search Engine designers are making intelligent systems that can rank a webpage based on its true purpose and just not only with too many searchable keywords by scraping keywords.

    The worst part is, if we attempt to populate too many computer friendly keywords, then it is more likely that people who actually read your content might end up disliking it. Search Engines these days consider the user satisfaction level also in their ranking logic. To succeed, a publisher has to balance both (Computer Vs Human).

    Also Read: Check If Outlook is Running - then Launch Outlook app - vba code

    Web And Keyword Scrape for SEO

    If there are only few pages, then the above points can be verified manually. What if there are 50 or 100 or even more pages? Use few available SEO page testing tools or develop your own tool with VBA web scraping explained below.

    Sometimes SEO plugins (of course, in free editions) might pose few limitations (like only 5 Articles for SEO Review per month, etc.,), creating our own tool will come as handy to overcome these situations and we can also do the required customization as per our need.

    This VBA code will make Excel retrieve data from website (scraping keywords from Web), parse the HTML tags in it and list down the below mentioned web analytic data points.

    • Headers (H1, H2, and so on till H6)
    • Text that are Bold
    • HREF link to Internal/External web pages
    • Scripts present in the page
    • Occurrences of Keywords in Content

    This is something that every Blogger has to verify in any of his article to ensure that webpage SEO task is complete.

    Tools for SEO: Web Scraping VBA Code

    If you are preparing a blog/article, you would be having a few keywords from Google Keyword Planner that are related to the keywords. For example, consider this post. which explains about a messenger option.

    Article: How to Activate Whatsapp Voice call feature?

    By reading the header of this article, reader and computer, both should understand -what the topic is all about. Always prepare a list of keywords about what you are going to explain; then prepare content with clear explanations. In my topic I chose ‘Whatsapp’ & ‘Voice Call Feature’ as focus keywords.

    If these keywords appear in any of the headers and my content explains concepts with these words, then search engines will index my page for those words. That means it assumes that My page explains something about Whatsapp and its features, so bookmark these pages and display in search result if someone searches for these words.

    Download Keyword Scraping Excel Downloaded 362 Times

    So, to improve the importance of these words, put them in Headers and bold it in appropriate places inside the content. That’s it. You now know how to do a basic level of SEO for a website. But, take much care about number of times you are repeating the keywords, if it occurs too often, then Search engines will mark it as spam and your page rank will go down.

    Also Read: Convert Excel to HTML Table Tags - VBA Macro Code

    How to Scrap a Website Keywords for SEO?

    We can now verify whether our topic has proper headers, important keywords are placed in proper tags and repeated appropriately, all links going out of webpage are not spam and any scripts are making unwanted execution.

    This tool can report you how many times the keyword is repeated, but not whether it is valid or not, because every search engine has its own criteria on this allowed number of repetition for a keyword. Try a research on that and modify your article accordingly.

    It is possible that most of us use many plug-in for our WordPress blogs and few might be inserting links to refer the developer websites. With the above code, we will get to know what are the external links out webpage is referring to and we take appropriate action based on the results.

    Summary
    Post Name
    Search Engine Optimization and Site Analyze with Free Tool
    Description
    How to do SEO for a Website? - Get Free VBA code that can extract web page and do basic SEO checks for your Blogs. Learn SEO basics and Build your own tools or Download & use the Tool available in this page.
    Kumarapush

    Previous Post:
    Next Post:


    • Ravish Mani

      Very informative post. Thanx for sharing 🙂