How To Create A Portfolio In Excel

Investing, Data Analytics, Excel, and Tableau

Create Custom Investment Analytics Dashboards with Excel and Tableau

Use Excel's STOCKHISTORY function to acquire stock price data and Tableau to build an investment analytics dashboard

"An investment in knowledge pays the best interest." — Benjamin Franklin

Introduction

Last night, as I reviewed my investments and the day's stock market news, an article about the September Effect caught my eye. People use the term to describe a supposed historical trend of low stock market returns in September. After skimming several more articles on the topic, I researched it further to learn whether it is accurate and whether it could affect my stock investments in September.

I pored over stock charts on my b r oker's website and Google and Yahoo. Those sites provide incredible investment analysis tools, but none offered quite what I needed to understand the September Effect's possible patterns better. So, I built my own dashboard, and it worked. The dashboard answered my questions about whether the stock market, in general, consistently declines in September.

This article will demonstrate how to obtain stock price data with Microsoft Excel and build a September Effect dashboard in Tableau. You could use the tools and techniques described here to perform various other investment analyses and track investments.

Note from Towards Data Science's editors: While we allow independent authors to publish articles in accordance with our rules and guidelines , we do not endorse each author's contribution. You should not rely on an author's works without seeking professional advice. See our Reader Terms for details.

Overview of September Effect Data Analytics Project

Below is a screenshot of the "NYSE Composite Price and the September Effect" Tableau dashboard that I built to help me understand the September Effect and whether it truly existed. It took less than two hours to build with Excel and Tableau Public. It should serve as a solid foundation for future custom investment analytics projects.

September Effect dashboard. Image by the author.

Here is an overview of the steps to build the dashboard:

In Excel, use the STOCKHISTORY function to retrieve the price of the New York Stock Exchange (NYSE) composite index on the first day of each month. For example, acquire price data from January 1966 through July 2021.
In Excel, calculate the price change and the price change ratio for each month. Excel calculates the ratio as follows: ratio = (Month2Price — Month1Price) / Month1Price. For example, the price on 1/1/1966 was $533.33, and the price on 2/1/1966 was $524.56. Thus, the change ratio for January 1966 was -0.0165533853, for a percentage change of -1.64%.
Open the Tableau desktop or Tableau Public desktop application. Then, connect Tableau to the Excel spreadsheet and worksheet created in steps 1 and 2 above.
In Tableau, create a September Effect worksheet that shows the price of the NYSE index over time and the price change percentage by month. Also, create a worksheet that shows the average price change percentage by month (excluding the year) sorted by month. Finally, create another monthly change worksheet, but sort it by the price change percentage.
Assemble a Tableau dashboard that includes the three worksheets described above.
Publish the Tableau dashboard to Tableau Server or the Tableau Public server (in my case) to share it with end-users.

Using the STOCKHISTORY Function in Microsoft 365

Overview of Excel STOCKHISTORY Function

I have used Excel for years but had not heard of its STOCKHISTORY function until I searched in Google for "stock history" last night while researching the September Effect. Discovering the function prompted me to build the investment dashboard. The function returns the price history of nearly any stock, ETF, mutual fund, and some security indexes over a specified time range.

Note that the STOCKHISTORY function works only in Internet-connected versions of Excel that are part of a Microsoft 365 subscription product. For example, I subscribe to Microsoft 365 Family, so I immediately put STOCKHISTORY to work.

Choosing a Composite Stock Index to use with STOCKHISTORY

STOCKHISTORY takes an investment instrument (a stock, ETF, or mutual fund) or composite index ticker as its first parameter. I initially wanted to use the Standard & Poor's 500 (S&P 500) composite index prices to analyze the September Effect. But STOCKHISTORY returned monthly values for only about the past ten years. So, after a bit of research on composite indices, I settled on the New York Stock Exchange (NYSE) composite index.

The NYSE is an American stock exchange that acquired its first traded securities in 1792. By far the largest stock exchange globally, the market capitalization of its listed companies totaled $30.1 trillion in February 2018. Investors trade billions of dollars on the exchange on every trading day.

The New York Stock Exchange established its composite index (ticker symbol: NYA) in the mid-1960s. The exchange assigned the index an initial value of 50 points when it opened on December 31, 1965. In 2003, the NYSE reset the value of the index to 5,000 points, near where the market closed in 2002.

Today, the NYSE lists over 2,000 stocks. Some 1,600 of these are U.S. companies, while the rest are foreign companies. So, the index represents the stock market well. Contrast its size with the S&P 500 composite index that lists 500 large companies and the Dow Jones Industrial Average (DJIA) with 30 prominent companies.

Based on the size and history of the NYSE, it seemed a good fit for my needs. Plus, unlike the S&P 500 composite index, the STOCKHISTORY function returned monthly prices for the NYSE index from January 1966 through July 2021.

Acquiring NYSE Composite Index Prices with STOCKHISTORY

While many Excel functions can access data in multiple worksheet cells, they typically write and display data in a single cell. STOCKHISTORY, on the other hand, can "spill" data into other cells. View the screenshot below, for example. The STOCKHISTORY function retrieves monthly price data for over 55 years for ticker NYA (NYSE composite index). Thus, even though the function resides in cell A2, its results spill into the range A2:B69.

Using STOCKHISTORY function in Excel. Image by the author.

For my purposes, I called STOCKHISTORY as follows:

=STOCKHISTORY("NYA","1–1–1966","9–1–2021",2,0)

From left to right, here are the meanings of its parameters:

stock — The ticker symbol for a stock, EFT, mutual fund, other security, or index. In this case, "NYA" is the ticker symbol for the NYSE composite index.
start_date — The first date of the desired date range. In this case, the value is "1–1–1966."
end_date — The last date of the desired date range. In this case, the value is "9–1–2021."
interval — The time interval that each value represents. Allowed values are 0 (daily), 1 (weekly), and 2 (monthly). I chose 2 (monthly) because of my need to calculate value changes by month.

For more information about the STOCKHISTORY function and its parameters, see the link in the "Learn More" section below.

"I will tell you how to become rich. Close the doors. Be fearful when others are greedy. Be greedy when others are fearful." — Warren Buffett

Create the Excel Spreadsheet

To acquire NYSE composite index (ticker symbol NYW) value data and prepare it for use in Tableau, I created an Excel spreadsheet called "NYSE_September.xlsx" with a single worksheet called "NYA. When completed, it would contain all data needed for the planned Tableau September Effect dashboard in four columns.

Follow these steps to recreate the NYA worksheet:

In row 1, in columns A through D, enter column names of "Date," "Price," "Price Next Month," and "Change."
In cell A2, enter this formula as described in the section above: =STOCKHISTORY("NYA","1–1–1966","9–1–2021",2,0)
While in cell A2, press the [Enter] key. If the function works, date and price data should fill columns A and B through row 69. If not, ensure that you have an active Microsoft 365 subscription and that you have connected your computer to the Internet.
Format each date in column A as a Date value. To do so, right-click on the cell and click on [Format Cells…]. A dialog box will present a list of possible formats. Click on [Date] and then click on [OK].
Enter this formula into cell C2: =B3. Then, copy and paste the formula into cells C3 through C69. It may not be apparent while this column is necessary. In summary, its presence ensures that the formulas in cells C2 through C69 have all values they need to calculate its value.
Enter this formula into cell D2: =(B3-B2)/B2. Then, copy and paste this formula into cells D3 through D69. For each month, the formula calculates the ratio of the change in price from the current month to the next month.
Save the spreadsheet file.

The spreadsheet should look similar to the one shown below. Note the down arrow symbols to the right of the column names. They are for filters I added to help me examine the data.

Sample of finished NYSE composite index data spreadsheet. Image by the author.

Create the Tableau Visualizations and Dashboard

The NYSE composite index data have been acquired, prepared, and stored in an Excel file. The following summary steps describe connecting the file in Tableau Public desktop and building the visualizations. While I have used Tableau for over five years, it requires some trial and error. Therefore, a tutorial to describe every step to create the dashboard would extend beyond the purpose of this article.

Also, if you would like to use the free Tableau Public instead of the whole Tableau, note that you can save your work only to the Tableau Public website. That makes your visualization available to anyone worldwide. So, be sure to exclude confidential information from the site.

Install Tableau

To perform the following steps, you will need to install a copy of Tableau Desktop or Tableau Public Desktop. To install Tableau Public Desktop, follow the Tableau Public Home Page instructions and Download link listed in the "Learn More" section at the end of the article. The Tableau Public Desktop application and publishing on the Tableau Public site are free.

Connecting to the Data

Open the Tableau Desktop or Tableau Public Desktop application. Then, follow the steps listed below to connect to the Excel spreadsheet and worksheet. Refer to the screenshot for an example.

Under the "Connect" heading, click on [Excel].
In the "Open" dialog box, navigate to the folder where you stored the NYSE composite index spreadsheet file.
Click on the name of the spreadsheet and then click on [Open].

Connecting to an Excel file with Tableau. Image by the author.

Monthly index price data should be displayed in the "Data Source" window, as shown below.

The data from Excel has successfully loaded into Tableau. Image by the author.

Building the Worksheets and Dashboard

The Tableau project contains a dashboard called "NYSE and the September Effect." It includes three worksheets, called "September Effect," "Monthly Changes," and "Monthly Change Rank." The dashboard tells part of the story of the September Effect, at least using NYSE composite index data starting in 1966.

As mentioned above, I won't provide detailed instructions to assemble the worksheets and dashboard. Instead, screenshots of each will provide ideas on how they were made.

Getting Started with Tableau Public

If you're new to Tableau, I suggest that you start to learn how to use the tool by following these steps:

Install the free Tableau Public Desktop application. See the link in the "Learn More" section at the end of this article.
Create a free Tableau Public account so that you can save and publish your visualizations to the Tableau Public site.
Watch the free Tableau online training videos. See the training resource link in the "Learn More" section below for more information.

Create the Worksheets

The "September Effect" worksheet is shown below in the Tableau Public Desktop application. The line blue line graph depicts the monthly value of the NYSE composite index. The bar chart shows the monthly change of the index (increase or decrease).

Tableau NYSE and the September Effect visualizations.Tableau NYSE and the September Effect visualizations. Image by the author.

The "Monthly Changes" worksheet, displayed below, shows the average percentage change of the index by month as a bar chart.

Average NYSE composite index changes by month bar chart. Image by the author.

The "Monthly Change Rank" worksheet, shown below, is identical to the "Monthly Changes" worksheet, except that the bar chart is sorted, in descending order, by the monthly change percentage.

Average NYSE composite index changes by month bar chart sorted by change percentage in descending order. Image by the author.

After I created the worksheets, I assembled them into a dashboard, as shown below. Note that the time window of the three charts is affected by the "Date Range" filter located in the lower-right corner.

The September Effect Tableau Dashboard in the Tableau Public Desktop application. Image by the author.

Publish the Dashboard to Tableau Public

After refining and testing the dashboard and its underlying worksheets, I published it on the Tableau Public website. You can see and interact with the finished project here.

Conclusion

The project to acquire NYSE composite index data with Excel and its STOCKHISTORY function and the construction and use of the "September Effect" Tableau dashboard helped satisfy my curiosity. As I interacted with the dashboard, I understood the September Effect better. While I invest in stocks, the project helped to confirm my agreement with my "Money and Finance" class college professor when he declared, "The stock market isn't related to anything."

Through the September Effect project, I gained new knowledge and skills. I also developed new ways to analyze investment securities and markets. I intend to continue to use Excel, its STOCKHISTORY function, and Tableau to bolster my capabilities when existing investment analytics dashboards don't meet my analytical needs.

I hope that you gained new knowledge and ideas from this article, too. Thank you for reading.

"With a good perspective on history, we can have a better understanding of the past and present, and thus a clear vision of the future." — Carlos Slim Helu