Excel Stock Portfolio — How To Build?
Free Template Inside
In the ever-evolving finance sector, keeping up with market trends and adeptly managing your investment portfolio is key to reaching your financial goals. Excel is a powerful tool for investors, thanks to its broad capabilities in data analysis and manipulation. However, manually monitoring stock prices and evaluating portfolio performance is a time-consuming process. This is where financial data APIs come in handy. They bring real-time market data right into your Excel spreadsheets, making managing your investments much easier. You can download the Excel template spreadsheet to follow along with the article more easily here:
Portfolio_Tracker_Spreadsheet.xlsx
Excel and Financial Data APIs Stock Portfolio Spreadsheet Implementation
For those looking for an Excel spreadsheet solution that’s ready to go, check out our Excel add-in. It lets you pull in accurate information right into your spreadsheet. There’s also an article on how to set up a similar portfolio in Google Sheets if you’re interested.
It’s worth noting that making external URL requests in the desktop version of Excel doesn’t work for macOS users. But, the web version of Excel remains a viable option.
Why Incorporate Financial Data APIs into Your Excel Stock Portfolio?
Financial data APIs, sometimes referred to as market data APIs, grant automated access to extensive financial data, such as stock prices, company finances, economic metrics, and more. Linking your Excel spreadsheet with a financial data API enables the automatic fetching and refreshing of market data, guaranteeing that your portfolio assessments are always current with the latest data.
EODHD supplies all essential data for financial analysis. In this article, we’ll demonstrate the basics of using several EODHD APIs and how to integrate them into an Excel sheet:
Live API — for real-time monitoring of stock changes today
End-of-Day API — for charting stock price trends
Fundamentals API — for accessing key financial ratios
We suggest signing up for a free account to gain complimentary access to some of our APIs: https://eodhd.com/register
For comprehensive access needed for thorough stock analysis, consider our subscription options here: https://eodhd.com/pricing
Create Your Spreadsheet Layout
Set up your Excel spreadsheet to monitor key details — such as stock ticker symbols, trade dates, prices, quantities, total investment sums, and metrics evaluating portfolio performance. Usual columns are “Ticker,” “Company Name,” “Quantity,” “Purchase Price,” “Current Price,” and “Market Value.” Feel free to add more columns to suit your analysis needs, like dividend yield, sector, or any other pertinent data.
Linking to the API
Here’s where the real action begins. Utilize Excel’s Power Query to set up a link with the financial data API. It’s akin to constructing a conduit between your spreadsheet and the flowing data from the API.
To create a spreadsheet that updates dynamically, we’ll tap into Microsoft Excel’s capabilities that fetch data via external URL requests. The function most often deployed for this purpose is ‘=WEBSERVICE()’. Additionally, we’ll explore the benefits of the recently introduced ‘=TEXTSPLIT()’, which aids in automatically organizing the incoming data into a structured table format. Note, ‘=TEXTSPLIT’ is accessible only in Office 360 versions updated post-2022.
Handling API Requests
To efficiently import the required data into your Excel Stock Portfolio spreadsheet, it’s essential to refer to the API’s documentation page. This resource will help you grasp the structure of requests and the data received.
For the Live API, access the provided link and navigate through the page to locate the information you need. Comprehending the API’s functionality and possible outputs is crucial for its successful implementation.
Obtaining Live Stock Prices
Employ the API’s data fetching functionalities to obtain the current stock prices for each ticker symbol listed in your portfolio.
To insert the live data directly into a cell, use this function:
=WEBSERVICE("https://eodhd.com/api/real-time/"&B11&"."&$C$8&"?api_token="&$C$5&"&fmt=json&filter=close")In this formula, we reference cell values to specify the ticker (B11), the market ($C$8), and the API token ($C$5). The URL incorporates a filter parameter “&filter=close” to confine the Live API’s output to only the closing price. The ‘&fmt=json’ parameter formats the output as JSON, which is preferable for live data due to its structured nature, though CSV is also an option. The Live API documentation details all available filters.
Plot Stock Performance Using Historical Data
Including a price chart in your analysis significantly aids in identifying trends. To gather historical data for creating a line chart, we’ll utilize the End-of-Day API. This API offers daily historical data, ideal for analyzing stock price movements.
A price chart, generated for a selected stock from your portfolio from its purchase date, enhances trend analysis.
To integrate historical data for charting into your spreadsheet, apply this function:
=SUBSTITUTE(SUBSTITUTE(WEBSERVICE("https://eodhd.com/api/eod/"&$B$3&"."&Portfolio!$C$8&"?api_token="&Portfolio!$C$5&"&fmt=json&filter=adjusted_close&from="&TEXT($C$3,"YYYY-MM-DD")&"&period=d"),"[",""),"]","")This function utilizes cell values for the ticker ($B$3), market (Portfolio!$C$8), and API token (Portfolio!$C$5). For the date range, it formats the purchase date using ‘TEXT($C$3,”YYYY-MM-DD”)’. The ‘=SUBSTITUTE’ function removes square brackets ‘[]’ from the data’s start and end. To convert the consolidated data into a table, we first apply ‘=TEXTSPLIT’:
=TEXTSPLIT(B6,,",")Although this splits the cell data into a table, it’s formatted as text. To convert each entry to numbers, ‘=VALUE’ is used:
=VALUE(B8)For pairing each close price with its respective date, modify the initial formulas by adding ‘..&filter=date..’, then employ ‘=TEXTSPLIT’ and ‘=DATEVALUE’ similarly:
=SUBSTITUTE(SUBSTITUTE(WEBSERVICE("https://eodhd.com/api/eod/"&$B$3&"."&Portfolio!$C$8&"?api_token="&Portfolio!$C$5&"&fmt=json&filter=date&from="&TEXT($C$3,"YYYY-MM-DD")&"&period=d"),"[",""),"]","")
=TEXTSPLIT(A6,,",")
=TEXT(DATEVALUE(RIGHT(LEFT(A8,LEN(A8)-1),LEN(A8)-2)),"YYYY-MM-DD")Selection for the stock is done using a ‘Dropdown’ menu from a specified range, with the date automatically updated through the ‘=VLOOKUP’ function.
Fundamental Data
A fundamental point of investment analysis is the evaluation of corporate financial ratios. In our spreadsheet, we leverage the Fundamental API to access crucial analytical data for the stocks within our portfolio.
To integrate fundamental data into the spreadsheet, employ this function:
=WEBSERVICE("https://eodhd.com/api/fundamentals/"&$B$3&"."&Portfolio!$C$8&"?api_token="&Portfolio!$C$5&"&filter=Highlights")Like before, we use cell references for the ticker ($B$3), market (Portfolio!$C$8), and API token (Portfolio!$C$5). We specifically request the ‘Highlights’ segment from the Fundamental API’s output using ‘&filter=Highlights’ in our URL to pull only the essential ratios into our sheet.
For a complete listing, this time, the ‘=TEXTSPLIT’ function is utilized to format the data into a neat 2-column table, with a sample cell formula as follows:
=TEXTSPLIT(RIGHT(LEFT(B6,LEN(B6)-1),LEN(B6)-2),":",",")Calculate Portfolio Performance
You can use Excel’s algebraic formulas to compute metrics like total portfolio value, unrealized gains or losses, and overall portfolio returns.
Automate Data Updates
Prefer not to update your data manually? Utilize Excel’s Data Connection Manager to automate regular data refreshes. This ensures your portfolio analysis remains current, continuously, without needing your direct intervention.
Leveraging Financial Data APIs with Excel
Merging Excel with financial data APIs essentially turbocharges your investment management:
- Real-time Access to Data: Fetch the newest stock prices and market data without the hassle of manual updates.
- Fewer Errors: No more typos or calculation mistakes that could skew your analysis.
- Deeper Portfolio Insights: Automatically calculate and gain richer insights into how your investments are performing.
- Customized Data Retrieval: Fine-tune the data you pull from the API to meet the unique needs of your portfolio.
- Easy to Scale: Effortlessly handle an expanding portfolio without getting bogged down by manual data entry.
Conclusion
Utilizing financial data APIs alongside Excel’s robust analytical tools revolutionizes your approach to investment management, making it more streamlined, precise, and driven by up-to-date information. This combination of financial data APIs and a customizable Excel stock portfolio spreadsheet empowers you with the knowledge to make well-informed investment choices, confidently navigating the complexities of the financial world.
For those eager to delve deeper into such insightful articles and broaden their understanding of different strategies in financial markets, we invite you to follow our account and subscribe for email notifications.
We publish 2 pieces per week!
Stay tuned for more valuable articles that aim to enhance your data science skills and market analysis capabilities.
