Issues with getting RSS feed from reddit into excel

Question

I have an excel sheet that all of my colleagues and I share and open each day. I have been thinking and trying to get an RSS feed from Top comment on /r/Jokes from the last 24 hours into being displayed in excel so that it would shows a new joke each day potentially.

I have tried a few things which didn't work as planning but one of the results was close:

1. When just using https://www.reddit.com/r/Jokes/top/.rss?sort=top&t=day it "breaks" my Excel meaning: It simply stops responding and dies. I have tried this on 3 computers didn't work on any.

2. I then tried making an account at https://fetchrss.com/ which gives exactly what I want it to look like. picture

Again this is only something I've been trying as I was unable to get "raw" data directly from reddit.

Below is not a definitive way I want to go as I don't think I'm doing it right or it may just be impossible to do it like that. So I made an account and registered to get the link http://fetchrss.com/rss/5b0a9c9a8a93f83d798b4567795678325.xml where it shows only the first result (the top one) which is fine.

What I tried was get the data in the excel sheet deleting the unnecessary columns and in a new colum write =stripHTML(B3) as this was the only function I could find on the internet that would remove the HTML codes. That is of course not working as intended as I want the "writing" HTML codes such as parentheses, quote to actually be visible in the sheet this is what it looked like what I tried to fix it with tape image (I am also a little unsure if this method works at all, meaning if it actually shows the data automatically in the sheet each day? I want this to be "automatically" meaning someone shouldn't have to refresh the data every morning to see something new)

So to sum up what i'm trying to do

• I'm trying to get a "live" feed from https://www.reddit.com/r/Jokes/top/?sort=top&t=day to be shown in the excel sheet.

• Preferably it should ONLY be the "top" comment of the day.

• Preferably I would like the RSS feed to come directly from reddit, but if that is not possible then i'm OK with using a alternate site it can go through like http://fetchrss.com/rss/5b0a9c9a8a93f83d798b4567795678325.xml

I hope it is possibly, thank you.

Answer 1

Made a post on Reddit as well and got the solution there.

When getting the data from Reddit, it is a lot better to use JSON with Power Query to get it to work.

So I used https://www.reddit.com/r/Jokes/top/.json?sort=top&t=day and followed his step as shown in https://streamable.com/0mqhu

Data -> From Web -> HTTP -> Fixed the settings to my linking 

and then it worked like a charm.

I also had to manually

Data -> Connections -> Properties

on the query after in order to change the update time, so that it would update every time the excel sheet was opened.

For anyone possibly wanting to use this in the future.

If you're on 2010 or 2013, you'll need to Download Microsoft Power Query for Excel from Official Microsoft Download Center.