Removed

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
acanthurus
Posts: 378
Joined: Sun Aug 04, 2013 8:02 am

Removed

Post by acanthurus » Wed Feb 21, 2018 11:35 am

Removed
Last edited by acanthurus on Sun Apr 08, 2018 10:29 am, edited 1 time in total.

fatcharlie
Posts: 37
Joined: Wed Aug 06, 2014 11:25 am

Re: How to lookup bond CUSIP in Google Sheets?

Post by fatcharlie » Wed Feb 21, 2018 5:22 pm

I don't know about looking up prices directly via CUSIPs (CUSIPs are a pain to use), but FRED allows you to lookup the prices of treasuries, forex and lots of other stuff:

Google Scripts function to do this:

Code: Select all

function Observations(fredId)
{
  var apiKey = "YOUR FRED API KEY";
  var url = "https://api.stlouisfed.org/fred/series/observations";
  url += "?file_type=json";
  url += "&realtime_start=2009-01-01";
  url += "&series_id=" + fredId;
  url += "&sort_order=desc";
  url += "&limit=100000";
  url += "&api_key=" + apiKey
  var response = UrlFetchApp.fetch(url);
  var results = JSON.parse(response.getContentText());
  var observations = results["observations"];
  return observations;
}
You can use CPIAUCNS for inflation, DGS10 for 10-year treasuries etc.

robcar5960
Posts: 1
Joined: Thu Feb 22, 2018 6:06 pm

Re: How to lookup bond CUSIP in Google Sheets?

Post by robcar5960 » Fri Feb 23, 2018 1:11 pm

Hi -

I like the idea of retrieving FRED data in a Google sheet. Is it possible for you to share a Google sheet with this capability or show a screenshot of the output from this function?

Thanks

User avatar
LadyGeek
Site Admin
Posts: 49146
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: How to lookup bond CUSIP in Google Sheets?

Post by LadyGeek » Fri Feb 23, 2018 2:44 pm

This thread is now in the Investing - Theory, News & General forum (general question).

robcar5960, Welcome!
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
sheetzilla
Posts: 32
Joined: Sat Jan 10, 2015 4:07 pm

fetching FRED data to Google Sheets

Post by sheetzilla » Fri Mar 02, 2018 9:34 pm

robcar5960 wrote:
Fri Feb 23, 2018 1:11 pm
Hi -

I like the idea of retrieving FRED data in a Google sheet. Is it possible for you to share a Google sheet with this capability or show a screenshot of the output from this function?

Thanks
robcar,

This is completely off topic to the original question, but here is how I am retrieving some FRED data into Google Sheets:

Code: Select all

cell: contents
K3: T30YIEM
L3: =index(IMPORTHTML(CONCATENATE("https://fred.stlouisfed.org/series/", K3), "Table", 1), 1,1)
M3: =index(IMPORTHTML(CONCATENATE("https://fred.stlouisfed.org/series/", K3), "Table", 1), 1,2)/100
N3: =ImportXML(CONCATENATE("https://fred.stlouisfed.org/series/", K3), "//title/text()")
gives you:

Code: Select all

T30YIEM	Feb 2018:	2.14%	30-year Breakeven Inflation Rate | FRED | St. Louis Fed
Some other interesting ones are: T5YIFR
MORTGAGE30US
MORTGAGE15US
FPCPITOTLZGUSA
DLTIIT
TB1YR
DGS10
DGS20
DGS30
DFii5
DFII10
Just go to the FRED page you're interested in and the code (or whatever it is) is listed at the top and in the URL.

It's definitely less elegant than fatcharlie's way, but you don't have to learn Google Sheets scripts.

Hope this is interesting for somebody.

-sz

Post Reply