8

How to extract Twitter tweets data and followers to Excel

In this tutorial,I will show you how to extract or scrape Twitter data such as tweets and followers to excel using ready made Python scripts.I will also show you how to download photos and videos by a #hashtag or search query.I will use both the REST API and the streaming API.Lastly,I will use Quintly to download tweets to Excel (no programming involved).

Below image shows extracted Tweets to Excel
extracted tweets to excel

Below image shows extracted Followers to Excel
extracted twitter followers to excel

The scripts I will share with you are complete working scripts.Even if you are not very familiar with Programming languages or not familiar with Python per se through the simple instructions outlined in this tutorial you can be able to extract data from Twitter.

The tutorial is divided into 4 parts

  • How to extract or scrape Twitter tweets to Excel with columns of tweet’s text,retweet count and favourite count using REST API
  • How to extract or scrape Twitter followers usernames or screennames to Excel using REST API
  • How to download photos and videos by a #hashtag or search query using Streaming API
  • Using Quintly to download Twitter data to Excel without programming

Let’s get started

Requirements

In order to run the scripts in this tutorial, you must install

How to install Python 2.7.x

For Windows and Linux/Ubuntu users follow Step 2 of this tutorial to install Python.

Mac OS X 10.8 comes with Python 2.7 pre-installed by Apple.If not installed you can install it from the command line by writing brew install python but make sure you have brew installed on your MAC.

install python on mac os x

How to install PIP

PIP is a python tool for installing and managing Python packages

For Windows:

  • In order to install pip go to this link and download get-pip.py to your computer by selecting “save link as…” from the pop up window.
    installing pip on windows
  • Then click or open the file to run it
    installing pip on command line
  • Before we can use PIP we need to add the Python scripts folder to the windows path variable.Open the CMD or PowerShell copy and paste below values then close the CMD or Powershell and reopen.
    [Environment]::SetEnvironmentVariable(“Path”,”$env:Path;C:\Python27\Scripts”, “User”)

For MAC OS X:

PIP comes by default with Python for MAC.Once Python is installed we can use PIP right away.

pip installation on mac os

How to install Tweepy

After Python and PIP are installed successfully then we have to install a Python library called Tweepy which enables us to access the Twitter API using Python.

You can install Tweepy by running the following command on the command line.

pip install tweepy

how to install tweepy by pip

Get authorization credentials to access Twitter API

In  order to access and download data from Twitter API, you need to have credentials such as keys and access tokens.You get them by simply creating an APP with Twitter.

Follow these steps:

  • Go to Twitter Application Management and log in,with your Twitter account
  • Click “create New app” button
  • Supply the necessary required fields, read and agree to the Twitter Developer Agreement
  • Submit the form

If everything went fine you will have a window similar to the one below with your keys and access tokens under the keys and access tokens tab.We will use these credentials in our example codes.

twitter api consumer key and consumer secret keytwitter api access token and access token secret

Now that we have all the requirements met let us extract some data to excel.

How to extract or scrape Twitter tweets to Excel using REST API

Download tweets.py script and save it somewhere on your computer.Put the script inside a folder.This is the same folder which the script will save the Excel spreadsheet after scraping tweets.The script will extract a user’s recent tweets up to a maximum of 3200.The script will get the following data:tweet ID, the tweet’s created time,tweet’s text,tweet’s retweet count and tweet’s favourite count.

You can modify the script to return more data.Here is a link which shows the structure of a tweet object together with its attributes.

Now from PowerShell for Windows and Terminal program for Mac OS X/Linux/UNIX navigate and open this folder containing your script.

For example, my script is saved inside “Scraping folder” on my desktop

python script to download a user tweets to excel

Before running the code you need to edit the code to include the credentials provided by the Twitter App Management interface in the previous step.

#Twitter API credentials
consumer_key = "Your consumer key goes here"
consumer_secret = "Your consumer secret goes here"
access_key = "Your access key goes here"
access_secret = "Your access secret goes here"

Also, input twitter username you want to download tweets from.In this example, we will scrape Donald Trump twitter page.

if __name__ == '__main__':
    #pass in the username of the account you want to download
    get_all_tweets("realDonaldTrump") #Modify this username

Run the code from the command line by typing python tweets.py but make sure while you are on CMD window navigate to the same folder(my script is in scraping folder)  where your script is saved.

downloading tweets to excel

3203 recent tweets of Donald Trump downloaded.

scrape tweets to excel

The Excel spreadsheet will be in the same folder where you saved your script earlier.See how the tweet “Mexico will pay for the wall” has been re-tweeted and Favorited.

extracted tweets to excel

How to extract or scrape Twitter followers usernames to Excel using REST API

Download followers.py script and save it somewhere on your computer.Put the script inside a folder.This is the same folder which the script will save the Excel spreadsheet after scraping tweets.

The script will extract a user’s followers to Excel spreadsheet.This scripts stops after every 300 names for 15 minutes, and then continues. This makes sure that it doesn’t run into problems. This will obviously take ages for large accounts.You basically just have to leave the program running if you want the next set.

Twitter has put limitations on the number of user which can be searched for a certain period of time.Otherwise you can be rate limited.

If you want to terminate the program at any time just press CTRL+C.

Before running the script modify the API credentials and the username whose followers you want to download.

accountvar = "realDonaldTrump" #Modify this username
#todo: upgrade this to read usernames from a file.
print "searching for followers of "+accountvar

consumer_key = "Your consumer key goes here"
consumer_secret = "Your consumer secret goes here"
access_token = "Your access key goes here"
access_token_secret = "Your access secret goes here"

Like previous step you run the script by wrting python followers.py on the command line.

extracting twitter followers to excel

Rate limit reached after extracting 300 followers and then sleeping for 891 seconds(15 minutes)

twitter rate limit reached

Below is the Excel spreadsheet in the same folder where you saved your script earlier with the extracted followers screen names,their followers count,statuses count and their locations.

extracted twitter followers in excel

How to download Tweets,photos and videos by a #hashtag or search query using Streaming API

Streaming API allows us to listen and grab live Twitter data.Using the scripts you can listen and grab any tweet which mentions a certain term or hashtag let say “#Football” in this tutorial and save the tweet to Excel and it’s associated media picture or video to the same folder which contains your script.

Download streaming.py script and save it somewhere on your computer.Put the script inside a folder.This is the same folder which the script will save the Excel spreadsheet after scraping tweets.

Before running the script modify the API credentials and input the search query you want to search.Follow these guidelines on how to build a query for better search results.

# Authentication details. To  obtain these visit dev.twitter.com
consumer_key = "Your consumer key goes here"
consumer_secret = "Your consumer secret goes here"
access_token = "Your access key goes here"
access_token_secret = "Your access secret goes here"

accountvar = "#Football"  #Search query goes here

Like previous step you run the script by wrting python streaming.py on the command line.

extract tweet images and videos from twitter

The script will generate an Excel spreadsheet,images and videos associated with #Football in real time.It will run continuously until you terminate it by pressing CTRL+C.

extract tweets using streaming API

Using Quintly to download Twitter data to Excel without programming

Now I will show you how to extract Twitter data into Excel using Quintly without programming.Quintly is a web-based tool that offers easy,efficient and  automated access to social media profiles.

It provides in-depth analytics, competitive benchmarking, graphs and reports, not only for Twitter but across all Social Media platforms such as Facebook, LinkedIn, Google+, Pinterest and Instagram.

You can use Quintly not only for your own profiles but for any public social media profile including all public Facebook pages and Twitter accounts.You can benchmark up to 100 profiles.
Quintly_banner
With Quintly Twitter analytics you get the following information on your dashboard

  • Follower Analysis– such as the followers count,the followers change rate of your profile or competitor’s profile
  • Detailed Tweet Analysis – such as the number of retweets and favorites of your profile or competitor’s profile
  • Interaction Analytics -retweets, replies and favorites directly on tweets of your profile or competitor’s profile
  • Key Interacting Users -Identify the key interacting users of your profile or competitor’s profile
  • Customer Care Metrics – Sustainably compare the customer care efforts of your profile or competitor’s profile such as response times to user questions and other requests.

Step 1:Sign up for a free account with Quintly

A free trial account will enable you to try all of the packages for 14 days without putting in a credit card. You will be able to export data into Excel or Powerpoint.

quintly twitter analytics price

Step 2:Add a profile

After creating an account with Quintly, go to “Manage Profiles” and then click “Add Profile”.

quintly manage profiles

On the pop up window search for the Twitter account (Example:Donald Trump) that you want to analyse and then click on “Add Selected Profiles” as shown below.

quintly add twitter profile

Step 3:Analysing Twitter data and exporting to Excel

Click  “Twitter” on the dashboard menu, and then choose any metric you want to analyse on the left side table such as profile Tweets and Followers. You can also specify the date range (don’t forget that you are limited to within 30 days for a free account).

Below I selected “Own and Retweeted Tweets table” so that I can replicate what we did in the previous steps without programming.Remember the tweet “Mexico will pay for the wall!”

Quintly twitter analytics dashboard

Once you have done this, export the results into either Excel, CSV, JPG, PNG, PDF or PPTX for further analysis.

extract twitter data to excel with quintly without programming

Conclusion

The tutorial just covered few attributes we can extract from a tweet object.You can modify the codes and extract more tweet object attributes.Here you can find a full list of a tweet object attributes in JSON format returned by Twitter API.

If you have any question or comment please let me know below.

How to Save Twitter Videos, GIFs and Pictures on PC
Click Here to Leave a Comment Below 8 comments