Cryptocurrency prices are susceptible to news information. Therefore, getting the right news around the crypto world is particularly important for crypto traders. However, it is not possible for you to find and read all such news as soon as they are published. As such, it is more beneficial to create your own news feed that can gather all the related news in one place. So, in this post, I will show you how you can create a crypto news feed in Google Sheets and pull news from various new sites.
You may also be interested in, Create A Crypto Portfolio Tracker in Google Sheets.
Table of Contents
Video Tutorial
If you don’t like the video or need more instructions, then continue reading.
How to find the news feed URL of a website
On the World Wide Web, a web feed (or news feed) is a data format used for providing users with frequently updated content [Wikipedia].
This data feed is in XML format. For example, you can see the news feed of this blog from the following RSS Feed URL. It contains all the latest content of this blog.
How to find the RSS feed URL of other sites,
- Around 40% of the websites on the internet are created with WordPress. Add “/feed” to the end of the WordPress URL (i.e. https://www.bpwebs.com/feed) to get the RSS feed. If you are unsure what type of website it is, just try adding “/feed” to the URL.
- Some websites advertise their RSS feed URL by showing the RSS icon . If you find this icon just click it to get the RSS feed.
- If non of the above methods are available, right-click on the web page and click the View page source. Then press Ctrl + F and type “RSS” or “Feed” to find the URL similar to the above.
How to get a news feed to Google Sheets
If you visit a feed URL, you will find a page with text and various HTML elements. It is in XML format. This content in the feed URL is not created for humans. It is created for feed readers and RSS crawlers. So, you need to have a feed reader to convert them to a human-readable format.
The data format mentioned above mainly contains, the news title, news URL, summary, author, and date created. Since the data feed is in XML format, you can convert them to tabular format programmatically.
With the Google Sheets IMPORTFEED function, you can import those news feed directly to the Google Sheets. With this, you can create your own feed reader in Google Sheets.
The IMPORTFEED function takes the following form,
IMPORTFEED(url, [query], [headers], [num_items])- URL – This is the feed URL we discussed above.
- [QUERY] – Optional. If you want to import only one of the RSS Feed, you can use this parameter. (i.e. “items title” of “items summary” etc…)
- [HEADERS] – Optional. Use “TRUE” or “FALSE” based on if you want to include the column headers. The default value is “FALSE”
- [NUM_ITEMS] -Optional. Define how many news items you want to retrieve. Usually, most websites provide 10 or 20 latest news items in their RSS feed. If you do not define the value it returns all the items.
For example, to get all the latest content of our blog to Google Sheets, you can use the IMPORTFEED function as below.
=IMPORTFEED("https://www.bpwebs.com/feed")How to create a Crypto News Feed in Google Sheets
The example I discuss below is not limited to crypto. You can use this method to create a personalized news feed in any subject you preferred.
First, you need to find a set of news feed URLs that provide crypto-related news. You may find news feeds that provide only crypto news or URLs (Ex. Forbes), which provide a mix of news. In this example, I going to use the following RSS feed URLs to import cryptocurrency-related news to Google Sheets. If you need to use more RSS feeds, you can find the URLs as explained above.
- https://www.forbes.com/money/feed
- https://news.bitcoin.com/feed/
- https://cointelegraph.com/rss
- https://blog.binance.us/rss/
Create a separate sheet for each feed and import all the feeds using the IMPORTFEED function
In this example, I am going to create 4 separate sheets for the above four websites (RSS feed URLs).
Place the IMPORTFEED in the A1 cell of each sheet. You can use the function as shown below to import the RSS feed with its column heading.
=IMPORTFEED("https://www.forbes.com/money/feed",,TRUE)Create a date column and extract the date from the “date created” column
The date-created column in the feed data is in the following format.
Tue, 22 Nov 2022 10:05:12 -0500We need to extract only the date from this field. It is required to filter the news by date. you can use the following formula to extract the date from the above string.
=DATEVALUE(MID(D2:D,FIND(",",D2:D)+2,11))We can use the ARRAYFORMULA to expand the above formula to the entire column as below. So, you can enter this equation in the first cell of the column and it will expand to the entire column.
=ARRAYFORMULA(IF(ISBLANK(D2:D),,DATEVALUE(MID(D2:D,FIND(",",D2:D)+2,11))))Add a new sheet to list filtered news
The News sheets you created as explained above contain a large amount of news. Some of them are published a few days back, or they may contain unrelated news. So, it is easy for you to read News if you import and filter the latest and related news to a new tab.
Therefore, let’s create a new sheet (tab) and name it “Today’s News”.
Filter and import Today’s news
The RSS feed of a website contains the latest news/posts of the website. If your selected website publishes only a few news/posts a day or a week, its RSS feed may contain old news also. In that case, you can use the QUERY formula as given below to filter and impost only today’s news.
=QUERY(bitcoin.com!A2:F21,"select A,C,D where F=date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'")The above formula imports the “Title”, “URL”, and “Date Created” columns (A,C, and D) from the bitocoin.com sheet. And also, it imports only the news created today (the date you view the Google Sheets).
Filter news by keywords
Let’s say you have found websites that occasionally publish excellent crypto-related news. However, their RSS feed is not limited to crypto news. In that case, you can use the method described below to extract only the crypto news from their RSS feed.
You can use the Google Sheets QUERY function to search records that contain a given set of characters and import them to a new sheet. Using this, you can search for some keywords related to cryptocurrency (ex: crypto, blockchain, etc…) in each news sheet you created earlier and import them to the “Today’s News” sheet.
You can use the QUERY function as shown below to search data by keywords.
=QUERY(Forbes!A2:F21,"select A,C,D where F= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and lower(A) matches '.*crypto.*'")The above formula search for the word “crypto” in column A (title) of the Forbes sheet. The matches string comparison operator is case-sensitive. Therefore, in the above function, I have used the “lower” scalar function on column A to make the comparison case-insensitive.
You can add more keywords as below.
=QUERY(Forbes!A2:F21,"select A,C,D where F= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and lower(A) matches '.*crypto.*|.*blockchain.*|.*fomc.*'")You can also add more columns to search for the keywords.
=QUERY(Forbes!A2:F21,"select A,C,D where F= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and (lower(A) matches '.*crypto.*' or lower(E) matches '.*crypto.*')")Combine all news into one list
In the above steps, we discussed how to filter and import news by date and search by keywords. Now, let’s combine all this news into one list.
To do this you need to select the same number of columns from each News tab. So, let’s import the “Title“, “URL“, and “Date Created” columns (A, C, and D) columns from all the news sites.
To do this, we need to create a single array by combining all the imported lists from each tab. You can use the curly brackets {} to create arrays in the following manner.
{imported list from tab 1; imported list from tab2; imported list from tab3}Accordingly, the following formula imports the filtered news from each tab. since all of Forbes’s news is not related to crypto, keyword search is applied to the Forbes tab.
={QUERY(Forbes!A2:F21,"select A,C,D where F= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and lower(A) matches '.*crypto.*'");
QUERY(bitcoin.com!A2:F21,"select A,C,D where F=date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'");
QUERY(Cointelegraph!A2:F21,"select A,C,D where F=date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'");
QUERY(Binance.us!A2:F21,"select A,C,D where F=date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'")}
If one of the QUERY functions combined above returns an empty output (#N/A), the above formula will return an error. To avoid this, you can wrap each query function with the IFERROR formula. Then, you can pass a meaningful full message as an array as shown below.
=IFERROR(YOUR_QUERY,{"Forbes: No Crypto News Today","",""}Accordingly, you can write the final formula as shown below.
={IFERROR(QUERY(Forbes!A2:F21,"select A,C,D where F= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and lower(A) matches '.*crypto.*'"),{"Forbes: No Crypto News","",""});
IFERROR(QUERY(bitcoin.com!A2:F21,"select A,C,D where F=date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'"),{"Bitcoin.com: No news today","",""});
IFERROR(QUERY(Cointelegraph!A2:F21,"select A,C,D where F=date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'"),{"Cointelegraph: No news today","",""});
IFERROR(QUERY(Binance.us!A2:F21,"select A,C,D where F=date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'"),{"Binance.us: No news today","",""})}
Now you have created your own crypto news feed. You can add more news from various news sites as explained above.
According to Google, the IMPORT functions check for updates every hour while the document is open, even if the formula and sheet don’t change (I could not find the exact documentation related to the IMPORTFEED function).
Email the newsfeed to your inbox automatically
If you can email the above news feed to your inbox, it is easier to read than it is in Google Sheets. Also, you will get a notification on your mobile device when you get the email. So, you won’t forget to read your newsfeed.
With Google Apps script you can automatically email the content in the Google Sheets at your preferred time intervals.
Write the Apps Script to email the newsfeed to the inbox
You can use the following code snippet to send the newsfeed (Today’s News sheet) you created in the above example to your inbox as an HTML table.
To use the above code,
- Go to Extension > Apps Script
- Replace the codes in the Code.gs file with the above script.
- Then change the Sheet name (line 3), recipient email (line 19), and subject (line 20).
- Click “Run“. When you run this code for the first time, it will request your authorization to run it in your account. If so, click “Review permissions“. Then choose an account (click on your email). Click the “Advanced” link, then click the link at the bottom (in my case “Go to Email Crypto News to Inbox (unsafe)“). Then click “Allow”. Then, click Run again.
Or, make a copy of the following Google Sheet to your Google Drive and start modifying. It included the script given above. Follow the above steps to open and run the code.
Set Triggers to run and send the emails at your preferred intervals
Now you are ready to set Trigger to automate the script and send emails to your inbox at your preferred intervals.
To set triggers,
- Go to Triggers (click the clock icon in your script editor menu on the left).
- Click the Ad Triggers button at the bottom right corner of the window.
- Select the Time-driven option from the “Select event source” dropdown list.
- If you want to receive the email daily, select the “Day timer” option from the “Select type of time base trigger” dropdown list.
- Select the time of the day you want to receive the email
- Click Save
Wrapping Up
In this post, we discussed how to create your own crypto news feed in Google Sheets. Further, we automated this Google Sheets to email the newsfeed to the inbox daily. And this is not limited to crypto news, you can use this method to create your own news feed on any subject you prefer.
