Social Media / July 15, 2020 / by Chris Makara

[HOW TO] Brilliantly Scrape Twitter Data With Microsoft Excel/Google Sheets

Who doesn’t love data? Not just Data who was in Goonies, but cold hard Twitter data?

Back in the day (I guess I am showing my age here), the character Data had all the gadgets in Goonies. No matter the situation he seemed to always have some contraption that bailed himself (and others) out of trouble. Twitter has tons of data to bail you out of a jam. Whether it is content creation ideas, targeting the right followers, or even seeing what your competition is up to.

While there are countless tools that provide insight into Twitter data, chances are you have said, “there has to be a way to do this in Microsoft Excel.” If not, now I might have you thinking.

The bad news is it is not a straight forward process. The good news is, I figured it out how to export Twitter data to Excel and will share the details on how to do it through some trial and error after reading this post from Matthew Barby. If you know of a better/cleaner way to construct any of the formulas, please let me know in the comments below.

MAJOR UPDATE  – The previous method described in this blog post no longer works. However, I did manage to create a Google Sheet that does. Click here to get the Google Sheet Twitter Scraper.

With this Excel spreadsheet, you will be able to pull in most data from any Twitter user, including first and last name, location, bio, URL, number of Twitter followers, number of Twitter users they are following, number of tweets, how long ago they last tweeted, and the content of their latest tweet. This info is very handy if you are trying to analyze certain Twitter users.

Need a More Advanced Twitter Scraper? Contact me to discuss the development of a custom Twitter scraper to get the Twitter data you need.

Excel’s Flux Capacitor

Flux-CapacitorThere is one component needed to make this happen. I’ll refer to another childhood movie I still enjoy…Like the great Dr. Emmett Brown in Back to The Future stated that the flux capacitor “is what makes time travel possible.”

While we are not exactly traveling through time here, Excel needs something that will allow us to pull external data in. To make this happen, we need to install Niels Bosma’s SEO Tools plugin.

I won’t go into all the awesome things this plugin can do, but if you are interested you can watch this.

Be sure to follow the installation steps outlined on Niels site. Once installed we can start building the dashboard.

Since the SEO Tools plugin is now installed, we can utilize a certain function called “XPathOnURL”. This, like the flux capacitor, is what makes importing Twitter data to Excel possible.

Don’t want to create the Excel file yourself?  It’s cool, I’ve got you covered. Click here to get my ready-to-go Excel file to scrape Twitter.

Importing Twitter Data into Microsoft Excel

For the purpose of this post and dashboard, I am going to strictly look at importing data from individual Twitter profiles. In order to pull in data, you will need a list of Twitter URLs that you want the data for.

In Excel, paste (or type) the full URLs of the Twitter users you want to get data for in column A. For example, http://www.twitter.com/ChrisMakara.

Next, we need to add the formulas needed in order to pull in the Twitter data to Excel. I won’t go into the details about how or why it works, it would more than likely be pretty boring! In the end, you will have working formulas to copy and paste into Excel.

So let’s get started playing with Twitter and Excel.

Twitter Name

To display the Twitter username, let’s name column B as “Username” and in cell B2 copy and paste the following formula:

  • =IFERROR(MID(A2,FIND(“=”,SUBSTITUTE(A2,”/”,”=”,LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””))))+1,256),””)

First Name

In column C, name the column “First Name”. Then in cell C2, paste this formula:

  • =IFERROR(LEFT(XPathOnUrl($A2,”//h1/a”),SEARCH(” “,XPathOnUrl($A2,”//h1/a”))-1),””)

Last Name

For column D, you need to name the column “Last Name”. In cell D2, use the following formula:

  • =TRIM(RIGHT(SUBSTITUTE(TRIM(XPathOnUrl($A2,”//h1/a”)),” “,REPT(” “,50)),50))

One thing to note about the “First Name” and “Last Name” columns is that these formulas will pull the information that the user has entered into the first name and last name fields on Twitter. There are some users who leave one or the other blank which will result in your spreadsheet not showing any values for these cells. Commonly this happens with business accounts who do not use a first and last name.

This holds true for other areas of a Twitter profile that users leave blank. So don’t be alarmed if you see empty cells throughout your Twitter Excel spreadsheet.

Bio

Next, let’s grab the bio information from the Twitter profile. So in column E, let’s label it as “Bio” and then use the following formula in cell E2:

  • =IFERROR(RIGHT(XPathOnUrl(A2,”//p[@class=’ProfileHeaderCard-bio u-dir’]”),LEN(XPathOnUrl(A2,”//p[@class=’ProfileHeaderCard-bio u-dir’]”))-0),””)

Location

To pull in the location of the Twitter user we will use Column F. Go ahead and name the column “Location” and paste this formula in cell F2:

  • =IFERROR(RIGHT(XPathOnUrl(A2,”//span[@class=’ProfileHeaderCard-locationText u-dir’]”),LEN(XPathOnUrl(A2,”//span[@class=’ProfileHeaderCard-locationText u-dir’]”))-0),””)

Personal URL

Nearly every Twitter profile includes a link to that user’s website. Lucky for you we can also pull this into Excel. In column G, let’s label it as “Personal URL” and then in cell G2 use this formula:

  • =IFERROR(RIGHT(XPathOnUrl(A2,”//span[@class=’ProfileHeaderCard-urlText u-dir’]”),LEN(XPathOnUrl(A2,”//span[@class=’ProfileHeaderCard-urlText u-dir’]”))-0),””)

Tweets

Up next is tweet count. This will pull in the total number of tweets for a user. In column H, label it as “Tweets Raw Data” and in cell H2 paste this formula:

  • =IFERROR(RIGHT(XPathOnUrl(A2,”//li[@class=’ProfileNav-item ProfileNav-item–tweets is-active’]”),LEN(XPathOnUrl(A2,”//li[@class=’ProfileNav-item ProfileNav-item–tweets is-active’]”))-7),””)

Through my testing, I noticed that for users that had over 10,000 tweets the data was showing as 10k. This made it difficult for me to sort columns ascending or descending for tweet count. Therefore we need to create another column that converts data like 10k to display as 10,000. So in column I, label it as “Tweets” and place this formula in cell I2:

  • =IFERROR(IF(ISNUMBER(-$H2),–$H2,LEFT($H2,LEN($H2)-1)*IF(RIGHT($H2,1)=”K”,1000,IF(RIGHT($H2,1)=”M”,1000000,1))),””)

Followers

To get the Twitter followers in Excel, label Column J “Followers Raw Data”. Then in cell J2 copy and paste this formula:

  • =IFERROR(RIGHT(XPathOnUrl(A2,”//li[@class=’ProfileNav-item ProfileNav-item–followers’]”),LEN(XPathOnUrl(A2,”//li[@class=’ProfileNav-item ProfileNav-item–followers’]”))-10),””)

Similar to the “Tweets” count, we need convert all data that is 10k or higher to display as a number. So in column K label it “Followers” and in cell K2 use the formula:

  • =IFERROR(IF(ISNUMBER(-$J2),–$J2,LEFT($J2,LEN($J2)-1)*IF(RIGHT($J2,1)=”K”,1000,IF(RIGHT($J2,1)=”M”,1000000,1))),””)

Following

Next, lets bring in the Twitter following in Excel. In column L label it as “Following Raw Data” and then in cell L2 paste the formula:

  • =IFERROR(RIGHT(XPathOnUrl(A2,”//li[@class=’ProfileNav-item ProfileNav-item–following’]”),LEN(XPathOnUrl(A2,”//li[@class=’ProfileNav-item ProfileNav-item–following’]”))-10),””)

Again we need to convert the data that is 10k or higher to show as a number. In column M, label it as “Following” and in cell M2 insert the formula:

  • =IFERROR(IF(ISNUMBER(-$L2),–$L2,LEFT($L2,LEN($L2)-1)*IF(RIGHT($L2,1)=”K”,1000,IF(RIGHT($L2,1)=”M”,1000000,1))),””)

Last Tweet Time

If you want to know how long it has been since the last time this user tweeted, this is how you do it. In column N, label it as “Last Tweet Time” and use this formula in cell N2:

  • =IFERROR(RIGHT(XPathOnUrl(A2,”//span[@class=’_timestamp js-short-timestamp js-relative-timestamp’]”),LEN(XPathOnUrl(A2,”//span[@class=’_timestamp js-short-timestamp js-relative-timestamp’]”))-0),”-“)

Last Tweet Text

The last piece of information we are going to extract into Excel is the content of a user’s last tweet. In column O label it “Last Tweet Text” and in cell O2 paste the following formula:

  • =IFERROR(RIGHT(XPathOnUrl(A2,”//p[@class=’TweetTextSize TweetTextSize–26px js-tweet-text tweet-text’]”),LEN(XPathOnUrl(A2,”//p[@class=’TweetTextSize TweetTextSize–26px js-tweet-text tweet-text’]”))-0),””)

Please note if the user has a “pinned” tweet, this is what will show up in Excel as their most recent tweet.

Don’t want to create the Excel file yourself?  It’s cool, I’ve got you covered. Click here to get my ready-to-go Excel file to scrape Twitter.

Tweaking the Excel File

Before we turn this loose, let’s tweak a few things in the Excel file. First, we want to turn this into a table so it makes it easier to play with (sorting, pivot tables, etc). To do this, simply click in cell A1 and then hit “CTRL+T” and hit enter.

Next, I prefer to hide a few columns in order to clean it up. Remember the additional columns we needed to create in order to change numbers greater than 10k to display as a number? So what I suggest doing is to hide columns H, J, & L. This will remove these columns from view (but still provide the data we need). Please note, do not delete these columns!

Let’s Get Some Twitter Data In Microsoft Excel

Now you are ready to pull in data from Twitter. The only thing missing is the list of URLs you want the data for. Simply type, or paste the Twitter URLs you want to find the data for starting in cell A2.

Once the URLs are entered, Excel will start pulling in the data. Depending on the number of URLs you are getting data for, it might take a while for Excel to get the data. I would not suggest pasting in hundreds of URLs at once. I like to keep to 50-100 at a time.

Excel Twitter Dashboard

When you have completed getting the data pulled into Excel, I like to copy all the data and paste the values into a separate spreadsheet. This will give you the raw data with the formulas removed preventing Excel trying to re-scrape the data again.

And if you find yourself having issues with scraping a lot of data or getting blocked, you’ll probably need to invest in proxies.

Now That You Have Twitter Data in Excel, How Will You Use It?

Once you have built your Twitter spreadsheet in Excel, how do you plan on using it?

  • Identifying the right users to follow/unfollow?
  • Spy on your competitors?
  • Study influencers within a certain location?
  • Find users to bulk manage into lists?
  • Look for patterns in the data?
  • Indentify content opportunities based on user bios & common personas?
  • Look out for fake Twitter accounts
  • Find users who have “Goonies” or “Back to the Future” in their bios?

Be sure to sound off in the comment section below letting me know how you plan on using this Excel Twitter Dashboard to your advantage!

Need a More Advanced Twitter Scraper? Contact me to discuss the development of a custom Twitter scraper to get the Twitter data you need.

Tags:
49 Comments
  • W October 13, 2014

    If I download the plug in will other people be able to open the same document (who havent previously downloaded the plugin) and get the live feed?

    • Chris Makara October 13, 2014

      The file should save the data you previously extracted. However, if you try to re-run the scraping on another computer, it will not work unless the other users have the SEO Tools plugin installed. The Excel file needs the XPathOnURL functionality from SEO Tools to scrape the data.

  • gnfb October 25, 2014

    is this still all working? I can’t seem to get the excel file suggested sent to my email is there somewhere to download it?

  • Claudia E. Mulder November 12, 2014

    Thanks Chris, very helpful. Does this work for Instagram (followers) as well? I tried fetching the XPath for Instagram followers, but I get a #VALUE! error in Excel. thanks, Claudia

  • Mr. J March 12, 2015

    Does this work on a Mac? I’ve just received the file, but I get no data when I start applying my URLs in Column A.

  • Junaid March 21, 2015

    Hellow Sir. i want to download all my tweets to an excel file. my username is @Yamira_o , kindly will you please do me a favor ? to send me my excel file to darulehsan2803@gmail.com ?

  • Jessica June 11, 2015

    Thank you, Chris, for sharing this! The first time I heard about Twitter Scraping was here http://bit.ly/1Dp1sRd and I liked it. What you wrote is more than useful.

  • Brian V August 19, 2015

    Everything works great EXCEPT the Last Tweet Time & Last Tweet Text. It’s blank. Any ideas?

    • Chris Makara August 21, 2015

      Hey Brian – Looks like Twitter tweaked something which was causing it not to work. I have updated the blog post with the revised formulas that should work. I tested on a handful of accounts and it seemed to populate results. Let me know if you have any issues.

  • Shit Flicks September 8, 2015

    Hi there, quick question. I’ve just managed to tweak your code to get the last tweet to work. However, how do you get the last 5 or say the last 10 tweets?

    • Chris Makara September 9, 2015

      Unfortunately I don’t think this is easily done in Excel…maybe it can be done with a VBA script, but I am not really any good writing those :/

  • Tanmay Kapoor December 14, 2015

    Hello Chris! I have tried all the steps you have mentioned in this blog but it’s not working for me. Not pulling up any data but it stays the same with formulas in columns. Please help…

    • Chris Makara December 14, 2015

      Did you install the SEO tools Excel plugin? If so, please send me an email at https://chrismakara.com/contact/ and we can go from there 🙂

      • Tanmay Kapoor December 15, 2015

        Yes, I installed SEO plugin. username column shows #name? and rest all columns stays blank…

        • Tanmay Kapoor December 15, 2015

          Thanks for sending me your file, it’s now working absolutely fine, just the way I wanted 🙂

          • Chris Makara December 15, 2015

            Glad you have it working and have found it useful 🙂

  • Demo January 30, 2016

    I can’t either download the sample file or join to your mailing list.Nothing happens when trying to submit the form. Multiple browsers tested.

  • greta February 4, 2016

    Hi Chris! For some reason I am unable to make Twitter followers into a raw number. E.g. I have got 163K, but want to have 163 256. Thanks!

    • Chris Makara February 4, 2016

      Did you use the formula mentioned above for Column M?

  • Jake March 29, 2016

    Hi Chris!

    Thanks for your work on this AND on the instagram one. Any chance you have a post on scraping facebook page likes or # of yelp reviews buried somewhere? Figured it was worth asking.

    Cheers!

    • Chris Makara March 29, 2016

      Hi Jake – I don’t have anything published about it. Are you just wanting to be able to enter a FB URL into Excel and then automatically get the # of likes?

  • satya June 22, 2016

    hi..Can I export brand post comments??

    • Chris Makara June 22, 2016

      Do you mean export comments from a tweet? I don’t think that will be possible with Excel, but would need to be done with a custom scraper. Please contact me if you need something like this developed – https://chrismakara.com/contact/

  • Brittany Smith April 3, 2017

    Hi Chris- the last tweet text formula does not seem to be working. Any recommendations?

    • Chris Makara April 3, 2017

      Try this:

      =IFERROR(RIGHT(XPathOnUrl(A2,”//span[@class=’_timestamp js-short-timestamp ‘]”),LEN(XPathOnUrl(A2,”//span[@class=’_timestamp js-short-timestamp ‘]”))-0),”-“)

  • Ahmad April 28, 2017

    Hi Chris.
    How can I pull the actual Lat/long of tweets that have geolocation

    • Chris Makara April 30, 2017

      Hmmm…not sure. Can you share the link to a tweet that has what you are looking for?

  • Melanie February 5, 2018

    This is great! I wish I would’ve seen it before I dropped 40 bucks on a freaking excel spreadsheet of an account’s followers! The spreadsheet has 15k followers, and I need to determine what percentage are fake. Do you have any thoughts on a formula that could be used to determine the following/follower ratio of all 15,000 users? I know accounts that follow hundreds and have few followers are fake, but there’s so many to sort through that viewing each users stats individually isn’t feasible. Thank you for this great article!

    • Chris Makara February 5, 2018

      You might be able to create a column with a formula that is =followers/followling. This will give you a ratio. From there you could use a pivot table to group the ratios to determine how many are on the lower end of the ratio?

  • Seth Graber February 20, 2018

    Wow, nice tool! Thanks for posting this. However, I am not able to get the formulas to work. I downloaded the SEO Tools for Excel as your first step noted. Is there something else I need to do to turn on the XPathOnUrl function? I’m getting the standard excel error for formula not recognized for each column.

    • Chris Makara February 20, 2018

      Did you enable the SEO Tools plugin in Excel? If so, you should see a tab for it in the ribbon of Excel…I just tested and it is working for me?

  • Anthony Pizzo December 18, 2018

    Hey –
    I am a PhD student working on my dissertation. I tried pricing how much an external service would charge to scrap a user’s history and it was in the thousands of USD. I am going to try this tonight. I will report back. Cheers and thanks – fingers crossed it works!
    Best,
    Anthony

    • Chris Makara December 18, 2018

      Cool, hopefully it will get you the data you need 🙂

  • Laura March 7, 2019

    Hi Chris,

    Thanks for sharing your work! It’s great that folks like you are still out there.

    Here’s my question:

    I’m running into a little problem ‘m hoping you can help with. When I add the Twitter URL in Column A only Username, First Name, Last Name, Bio, Location, and Personal URL are populating. Tweets, Followers, Following and Last Tweet Text are not. (Last Tweet Time sometimes populates and some times doesn’t. It’s hit or miss.) I downloaded your excel template with the formulas – so luckily we can rule out a formula typo on my part. Any ideas why these columns aren’t showing up? The SEO Plugin says “getting data” and magically all the columns I mentioned are filled in , but the others aren’t. I’ve tried a dozen different URLs and the result is always the same. Is there something I’m missing? Could Twitter recently have made some changes that require a different formula in these columns now?

    Thanks!
    Laura

    • Chris Makara March 8, 2019

      Hey Laura, thanks for the comment…I just sent you an email 🙂

  • John Kennedy May 10, 2020

    Hi Chris,

    Just tried some of these formulas and they returned formula errors. Wondering if these still worked or if Twitter has changed something on their backend? I’ve heard about issues in them changing from html to JS. Any help is appreciated. Thanks!

  • Cameron Jessamine May 20, 2020

    Hi Chris, just wondering if you have similar system for LinkedIn that would allow me to scrape Employees, Followers and Last Post Time of company pages? This scrape worked perfectly for Twitter data – saved me a tonne of time!

    • Chris Makara May 21, 2020

      Hey Cameron, I haven’t put one together for Linkedin…I would need to look into it a little closer to see if it is possible. Glad you found the Twitter one useful!

  • Eric July 1, 2020

    Hi Chris. Great post and thanks for sharing it.

    The SEOTools app isn’t allowed to authorize on Twitter which makes that feature set useless. The formulas for page scrape don’t work – Excel flags them with an error. The last two commenters have the same issue which you addressed privately. Would be great to know how to fix. Thanks.

    • Chris Makara July 10, 2020

      Hey Eric, I just updated the Google Sheet you should have received when signing up for it. Please follow the steps listed on it and let me know if that gives you what you needed.

  • Patrick Kazembe July 10, 2020

    Hey Chris Makara,

    Is it possible to pull bulk twitter Urls. I have a huge list of Twitter usernames I want to pull their Urls. Thanks

    • Chris Makara July 10, 2020

      Yes. I just updated the Google sheet you can by clicking the link in one of the yellow boxes above. You should be able to scrape about 500 usernames without it breaking/not working. Let me know if that will work for you.

Leave a comment