[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

There 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. 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.