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.
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.
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.
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.
To display the Twitter username, let’s name column B as “Username” and in cell B2 copy and paste the following formula:
In column C, name the column “First Name”. Then in cell C2, paste this formula:
For column D, you need to name the column “Last Name”. In cell D2, use the following formula:
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.
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:
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:
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:
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:
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:
To get the Twitter followers in Excel, label Column J “Followers Raw Data”. Then in cell J2 copy and paste this formula:
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:
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:
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:
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:
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:
Please note if the user has a “pinned” tweet, this is what will show up in Excel as their most recent tweet.
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!
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.
Once you have built your Twitter spreadsheet in Excel, how do you plan on using it?
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!
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?
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.
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?
Yes, you should have received a confirmation email. If you did not get the confirmation email, try submitting the form again or contact me through https://chrismakara.com/contact/.
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
Possibly. I would need to play around with it to see. If I figure it out, I’ll probably put it in a blog post 🙂
Just wanted to let you know I made one for Instagram 🙂 https://chrismakara.com/social-media/instagram-data-microsoft-excel/
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.
I don’t think so…according to http://nielsbosma.se/projects/seotools/download/ – the SEO Tools plugin does not work on a Mac…
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 firstname.lastname@example.org ?
Hi, you can do this by clicking the “request archive” button on https://twitter.com/settings/account.
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.
Everything works great EXCEPT the Last Tweet Time & Last Tweet Text. It’s blank. Any ideas?
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.
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?
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 :/
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…
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 🙂
Yes, I installed SEO plugin. username column shows #name? and rest all columns stays blank…
Thanks for sending me your file, it’s now working absolutely fine, just the way I wanted 🙂
Glad you have it working and have found it useful 🙂
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.
Sorry, for the trouble. I am working on fixing it. In the mean time, please contact me https://chrismakara.com/contact/ and I can send you the info.
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!
Did you use the formula mentioned above for Column M?
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.
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?
hi..Can I export brand post comments??
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/
Hi Chris- the last tweet text formula does not seem to be working. Any recommendations?
=IFERROR(RIGHT(XPathOnUrl(A2,”//span[@class=’_timestamp js-short-timestamp ‘]”),LEN(XPathOnUrl(A2,”//span[@class=’_timestamp js-short-timestamp ‘]”))-0),”-“)
How can I pull the actual Lat/long of tweets that have geolocation
Hmmm…not sure. Can you share the link to a tweet that has what you are looking for?
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!
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?
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.
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?
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!
Cool, hopefully it will get you the data you need 🙂
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?
Hey Laura, thanks for the comment…I just sent you an email 🙂
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!
Hey John, just sent you an email 🙂
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!
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!
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.
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.
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
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.