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
Below image shows extracted 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
In order to run the scripts in this tutorial, you must install
- Python 2.7 .x
- PIP(a tool for installing and managing Python packages)
- Tweepy (a python library for accessing the Twitter API)
- Any Python code editor
- You must have authorization credentials to access Twitter API
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.
How to install PIP
PIP is a python tool for installing and managing Python packages
- 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.
- Then click or open the file to run it
- 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.
For MAC OS X:
PIP comes by default with Python for MAC.Once Python is installed we can use PIP right away.
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
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.
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
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.
3203 recent tweets of Donald Trump downloaded.
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.
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.
Rate limit reached after extracting 300 followers and then sleeping for 891 seconds(15 minutes)
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.
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.
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.
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.
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
Step 2:Add a profile
After creating an account with Quintly, go to “Manage Profiles” and then click “Add Profile”.
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.
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!”
Once you have done this, export the results into either Excel, CSV, JPG, PNG, PDF or PPTX for further analysis.
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.