Import JSON data from external data sources (Power Query) into Excel 360 using an API

Today we will see how to import an Institution/fund manager stock information filed with SEC (https://www.sec.gov/) into Excel.

A fund manager is responsible for implementing a fund’s investing strategy and managing its portfolio trading activities. The fund can be managed by one person, by two people as co-managers, or by a team of three or more people. The main benefit of investing into a mutual fund / hedge fund is to optimize your return based on your risk profile. Using FmpCloud.io you can find out who this person is and then research the quality of their work. Here’s how:

As you can see on Investopedia:

“The Securities and Exchange Commission’s (SEC) Form 13F is a quarterly report that is required to be filed by all institutional investment managers with at least $100 million in assets under management. It discloses their equity holdings and can provide some insights into what the smart money is doing in the market. However, studies have found that 13F filings also have serious flaws and can’t be taken at face value.”

In other terms each big investment funds fills quarterly reports that shows you their portfolio positions (stocks holding) that you can analyse and understand by looking at which stock they hold at each quarter to see if they increase their position or decrease them (in other term rebalanced their portfolios).

Let’s look at one report:

SEC FORM 13-F Information Table

You can see all positions of Berkshire Hathaway inc for 2020–05–15 (first quarter of 2020).

We will now use a restFull API: fmpcloud.io : https://fmpcloud.io/ and Excel 360 : https://www.office.com/ to import this filings and analyse them through excel.

1. To search for the fund manager or institutional holdings, you need to know their CIK numbers, the CIK numbers are given by the SEC to identify the individuals or companies while filing financial statements. 2. The following steps can guide you through the process: (Let’s use Soros Fund Management as an example):

a. Log in to https://fmpcloud.io/

b. Scroll down the page until you see :

c. Click the link as the above arrow pointed to

d. The page will become this:

e. In the address bar: replace “Berkshire” with “Soro” and press Enter, the following screen will appear:

f. Copy the CIK number, in this case, it is going to be: 0001029160, then go back to the 13F section , scroll down to the “13F” section and click the link below:

g. Copy the above the link (arrow pointed to) onto the address bar, and replace the “49205” number with the CIK number you just copied, in this case it is: 0001029160, please make sure the other parts of this link are stay the same, then hit enter

h. You will see the following content appears:

i. The above page shows the Soro’s portfolio filed with the SEC, copy the link as arrow pointed to.

3. Data Extraction to Excel sheet:

a. We will use the Excel 365 to demonstrate the data import

b. Open the Excel 365, click the “Data” tab, and click “From Web”

c. Paste the link into the box that appeared int the excel sheet

d. Click, ok

e. In the open box, click the 2nd “Transfom” from the left to right as arrow point to.

f. Then click “To Table” Convert, the 1st option from the menu below

g. Click ok when the “To Table” box show up

h. Click the upper right icon when the box shows up again:

i. Choose the header, then select “OK”

j. Then click “close and load to table” from the box

k. The Full content will show into the excel sheet now.

We have now extracted all Soros Fund Management portfolio positions and we can analyse them, find if they acquire new stocks, liquidate some stocks and find new opportunities.

Originally published at https://fmpcloud.io.