stocks – Page 3 – HowtoExcel.net

Free Excel Template to Track Berkshire Hathaway’s Portfolio

September 13, 2022

by howtoexcel with No Comment free free template investing stocks template

Warren Buffett fans like to track the billionaire investor’s moves, and a good way to do that is through Berkshire Hathaway’s 13f filings. His company reports its holdings every quarter, showing where there were changes in its positions. By looking at multiple filings, investors can track the changes from one period to another. With this free template, you can do that quickly and easily all on your own. All you have to do is specify the specific filings that you want to compare to one another.

How the template works

This template uses PowerQuery to grab the data from the 13f filings. It will then compare the two to find the changes in share count.

There are only two inputs you need for the template, both are on the Current.Holdings tab. You’ll need to paste the URL for the current 13f filing and the previous one (or whichever filings you want to compare against one another). It can sometimes be a bit tricky to get exactly what you’re after. Here’s how you can find the 13f filings for Berkshire Hathaway to use in this template:

Click on the following link, which has company’s filings on the SEC website: https://www.sec.gov/edgar/browse/?CIK=1067983 (this link is included within the template).
Click on the View filings button on the page.
Do a search for 13f so that you can see just the 13f filings on that page:

Berkshire Hathaway's 13f filings on the SEC website.

4. Find the reporting period you want and click on the Filing button — don’t click on the actual description next to it.

5. When you open up the link, you should see multiple files you can open. Select the information table that is in html format:

Selecting the information table in HTML format from the SEC website.

When you open the file, you should see the holdings in a table format:

If this is what you see, then the link you’ve downloaded will work. Note, on older 13f filings (e.g. 2013 and older), the format is in a text file and they won’t work with this template.

6. Copy the link and enter it into one of the fields on this template, either for the new filing or the old one.

Entries for the old and new 13f filings.

7. Once you’ve filled in both the new and old links, then go to the Data tab in Excel and click on Refresh All. This will update the queries that the template relies on, and calculate the changes.

Now the different sheets will update:

Current.Holdings: this will show you the current holdings as per the New13f file
Change.In.Holdings: this will show you the change between the two filings. The change will be reflected in total shares and as a % of change in shares.
Old.Holdings: this will show the number of shares held per the Old13f file.

Summary of Berkshire Hathaway's holdings per their latest 13f filing.

Download the template

This template is completely free to use and you can download the Berkshire Hathaway 13f Template here.

If you like the Berkshire Hathaway 13f Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Calculating the Doubling Time Formula in Excel

July 19, 2022

by howtoexcel with No Comment financial analysis functions lambda stocks

Do you want to calculate how quickly it will take for something to double in value? In this post, I’ll show you how to calculate that using the doubling time formula. By utilizing variables, it can also be easily updated in Excel to factor in different growth rates, making it easy to do what-if calculations.

What is the doubling time formula?

The doubling time formula utilizes logarithms and takes an assumed growth rate to determine how long it will take for a value to double in value. For example, if your investment were to rise at a rate of 10% per year for 10 years, it would be worth roughly 2.59 times what it is now. But rather than doing trial and error to try and determine exactly at what point it will double in value, you can use a formula to do that for you.

In essence, all the doubling time formula involves is taking the logarithm of the change in value you’re trying to get to (e.g. 2) and dividing that by the logarithm of the current growth rate plus 1 (e.g. 1 + 0.1 = 1.1). By doing this calculation, you get an answer of 7.27 for this example. You can plug that into the following formula to check:

1.1^7.27

And the result will 1.9995. The more decimal places you keep in the above calculation, the closer you will get to precisely 2. This formula can also be adapted if you want to calculate how long it will take to triple, or quadruple. In those cases, you can just change the numerator so that instead of taking log 2, you’re taking log 3 or log 4, if you want to calculate tripling or quadrupling time, respectively.

Setting up the formula in Excel

As you can see, this isn’t a terribly complex formula. The key is really just using logarithmic functions in Excel. And whether you use a natural log or not doesn’t matter, your results will be the same. You can use the LOG function for these purposes. In Excel, the earlier formula would be calculated as follows:

=LOG(2)/LOG(1.1)

To make it more versatile, I’ll also add some variables here. One for the current growth rate, and one for the target growth (this is where you can specify if you want to double, triple, quadruple, etc.). Here’s how that looks:

A value of 2 will read as 200% in Excel. The formula to calculate the years to double will simply need to be adjusted to factor in for these variables, which I’ve named TargetGrowth and GrowthRate in my file:

=LOG(TargetGrowth)/LOG(1+GrowthRate)

By utilizing these variables, I can now easily update my calculations.

Creating a LAMBDA function to make it even easier

Another thing you can do is to create your own LAMBDA function. If you’re on the latest version of Excel, these are custom functions you can ease, without the need to even set up a template and separate cells. All this involves is going to the Name Manager in Excel as if you were creating a new named range (the long way). Except when you create it, the name you’re assigning is the name of the function. And rather than referencing cells, you’re entering in a formula.

This particular function should contain two variables, one for the current growth rate, and one for the target. It will then plug them into the formula I referenced above. Here’s what the formula will need to look like within the Name Manager:

=LAMBDA(current,target,LOG(target)/LOG(1+current))

You’ll notice it needs the LAMBDA prefix so that Excel knows to treat this differently. Here’s how it looks within the Name Manager:

I called it DoublingTime even though it can do more than just calculate that. You can of course call it whatever you prefer. Now, this formula can be used in Excel to do the exact same calculation as above, without the need for extra cells:

You’ll notice here I’m just entering in raw values as opposed to percentages. This is just because of how I structured the formula and to keep it as simple as possible.

If you liked this post on Calculating the Doubling Time Formula in Excel Functions, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Import Multiple Stock Tickers Into Excel Using Power Query

May 25, 2022

by howtoexcel with No Comment investing power query stocks

Power Query can allow you to easily import data from another spreadsheet. But did you know that you can load multiple files from a folder at once? All you need to do is load the files you want to import into a folder, and Power Query can do the rest. In this article, I’ll show you how you can do this with stock prices and how you can import multiple ticker files from Yahoo Finance into Power Query at once.

Put all the files into a single folder

Whatever type of files you want to import, the key thing is that their format is consistent. This is because Power Query will follow a similar process when importing them. If, for example, you always remove certain columns from a file, then you want to make sure that every file you import has those columns. If there’s a discrepancy, then Power Query may struggle to load the files properly.

In this example, I’m going to use CSV files from Yahoo Finance. Let’s say I want to download data for multiple stock tickers. If I go to Apple’s stock ticker page, there’s a link to download the latest stock prices. In a previous post, I went over how to download stock prices for a single ticker. This time around, I’ll show you how you can do it for as many as you want. If I want to download multiple tickers, I’ll start by downloading all the different CSV files for them and putting them into just a single folder:

Folder with CSV files for different stocks.

Here I’ve got multiple tickers downloaded, including Apple’s. This is now the folder I will reference when extracting the data from Power Query.

Importing the files Into Power Query

In Excel, the next step is to simply download the data. Under the Data tab, click on the Get Data button and select the option for From Folder:

Selecting to import files into Excel from a folder.

Then, navigate to the folder where your files are stored and click on Open. Now the Power Query window will load and you should see something like this:

Power Query window showing all the files in a specific folder.

Here I see all the files from my folder. There are three different options I can take at this point:

Transform Data. Clicking on this option will allow me to transform the table above.
Load. If I don’t want to make any transformations and just load the table above, this is what I’ll select. But like the above option, this will not combine the data, so this is not what I want.
Combine. This is the option that I will choose as it will combine all these files together. From here, you’ll have the option to Combine and Transform or to just Transform and Load (e.g. if you don’t need to make any adjustments).

On the next screen, you can click on OK and the combined data will be loaded. To make the process as seamless as possible, you’ll want to ensure that your files follow the same format. Otherwise, it can be more difficult to get the desired results.

After clicking on OK, now the data loads, and all my stock data from Yahoo Finance is downloaded, with all the different tickers:

Multiple stock files downloaded into Excel.

Now, you can add more downloads from Yahoo Finance for different tickers, put them in the same folder, and then just refresh the query. Your spreadsheet will now automatically update based on the CSV files within the folder.

If you liked this post on How to Import Multiple Stock Tickers Into Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Convert a Table From an SEC Report Into Excel

April 7, 2022

by howtoexcel with No Comment formatting functions iferror investing parse stocks substitute template

If you want to download a company’s financial statements or data, the easiest place is often straight from the source: the Securities & Exchange Commission (SEC). You can download financials in Excel format if there is an interactive option within the SEC filing, but that won’t give you all the tables contained in an earnings report. In this example, I’m going to use Adobe’s most recent earnings report to show you how to get a table into Excel

Downloading the data

Adobe’s earnings report is found here, with the following financials on page 4:

Adobe's income statement for the quarter ending March 4.

Copying it into Excel

Copy the table and then go to paste it data into Excel. But when you right-click in Excel, make sure to select the option to paste it so it matches the formatting on the sheet, as shown below:

Paste with a format matching the spreadsheet.

Now, the data pastes without any of the colors and formatting onto my Input sheet:

Adobe's earnings report downloaded into Excel.

If when you paste it doesn’t show up like this and it looks like just a few lines, re-try copying the data. It may help not to include the header that says “three months ended” and simply start copying from the first line item (“revenue” in the above example”) to ensure that Excel picks it up as a table.

Formatting the data

It looks pretty good except that I have many extra columns. And numbers that have dollar signs have been pushed out by one column. What I will do here is create a template in a separate sheet that will automatically pull in what is needed. The new tab, called Output, will be where I create my formulas. My assumption is that the spacing will be consistent and that the current period values are in columns D and E, and the ones from the prior-year period are in columns J and K.

Starting in cell A1, I’ll create a simple formula that checks if the same value on the other sheet is blank. If it isn’t, then it will pull in the value, otherwise, it will remain blank:

=IF(Input!A1="","",Input!A1)

I will do the same thing for column B, except this time I am looking at values from the Input tab in column D. And I will need to adjust for if there is a $ sign. If there is, I need to pull the value from column E instead. Here’s what that formula looks like:

=IF(Input!D1="","",IF(Input!D1="$",Input!E1,Input!D1))

That gets me a bit closer to where I want to get to:

Financial figures pulled into a separate tab with formatting applied.

There are still a couple of issues. The first is that on row 30, there is a symbol that isn’t a dash that I need to remove. This is character code #151. And there’s also a trailing blank space behind the numbers that needs to be removed. This isn’t your ordinary blank space and it is character code #160. I need a couple of SUBSTITUTE functions to remove those character codes:

=IF(Input!D1="","",SUBSTITUTE(SUBSTITUTE(IF(Input!D1="$",Input!E1,Input!D1),CHAR(160),""),CHAR(151),0))

For character 151, I want to replace this with a 0 value since that’s what the symbol is in place of. Next, I need to convert these values to numbers. I can do this by multiplying them by a factor of 1. I’m going to use the IFERROR function as well so that in case it’s text, it will return the original value in column D. Here’s my completed formula:

=IF(Input!D1="","",IFERROR(1*SUBSTITUTE(SUBSTITUTE(IF(Input!D1="$",Input!E1,Input!D1),CHAR(160),""),CHAR(151),0),Input!D1))

Now, I can repeat this formula in the adjacent column. Except this time instead of referencing D and E, I’ll refer to columns J and K. Now, my output tab looks as follows, after applying some formatting to it:

This can be re-used over for other tables in an SEC report, as they generally follow the same pattern. For example, this is Adobe’s table showing sales by segment:

By dropping this into my Input tab, this is what my Output now shows:

Output tab in Excel showing Adobe's segmented financial information.

All that I needed to do was to copy the formulas and just adjust the columns they referenced on the Input tab. If you’d like to use the file I’ve created for your own use, you can download it for free, from here.

If you liked this post on How to Convert a Table From an SEC Report Into Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Here’s How the S&P 500 Has Historically Performed After a Bad January

February 9, 2022

by howtoexcel with No Comment google sheets investing stocks

The stock market is off to a rough start to 2022, with the S&P 500 falling more than 5% in just one month. Using a spreadsheet, we can analyze historical trends and patterns to identify what normally happens after such bad starts. Below, I’ll use data from Google Sheets to pull in historical values and analyze how the index has performed afterward and whether this year is doomed to be a bad year, or if a recovery is likely and if now is a good time to invest in stocks.

Start with downloading the historical data

The first step is to get the S&P 500’s historical values in Google Sheets. This can be done using the GOOGLEFINANCE function. Using the .INX symbol, I can calculate the S&P 500 values going back to the 70s. Here’s a matrix showing the returns over the past 50 years, after applying some conditional formatting to the values:

Historical S&P 500 values in Google Sheets.

Filtering the data

To zero on in just the largest January declines, I can use the Filter by condition option to specify January values where the percent change is less than negative 5%:

That leaves me with the years when the S&P 500 dropped by 5% or more in the first month:

Now that I have a list of the years I’m looking to analyze, I can start creating some charts.

Using charts to summarize the performances

The first visual I’m going to create will look at how the index has performed after January, after those bad starts. To do that, I need to take the year-end values and divide them by the values at the end of January. This tells me how much the index rose or declined in the remaining months. And when grouping those variances, this is what the data shows:

S&P 500 returns after January in years where it declined by more than 5% in the first month.

Of the 7 previous times when the S&P 500 dropped 5% in January, 3 times it would continue to drop in the following months and finish even lower. Only two times would the index rise by more than 10%. I can also average the results, comparing the down years versus the overall average:

Average S&P 500 returns versus those returns in down years.

This tells me that in a year where the S&P 500 typically tanks in the first month, the overall returns from the index are likely to be negative. However, to add a bit more context to this, I’ll look at the individual returns by year and compare them against the 50-year average, which is summarized in this table:

Table showing the S&P 500 returns in years after a bad January versus the overall 50-year average.

By keeping the average column constant, it creates a straight line for the chart and makes it easy to visualize the individual years’ returns and how they compare against it:

S&P 500 annual returns in years where the index dropped by 5% in January versus the 50-year average.

A few of the things that stand out from the data is that in three of the years (2000, 2008, 2009), the markets were either in the midst of a significant crash or recovering from it. It helps put into context some of these returns, suggesting that the other years might indicate more typical returns in a non-crash year. And if that’s the case, investors may expect fairly modest returns this year, possibly negative ones overall. Although it isn’t a large data set, it certainly suggests that the stock market may be facing a down year in 2022.

You can check my calculations in the Google Sheets file I used to create this data.

If you liked this post on the S&P 500’s Historical Returns, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Historical Stock Returns by Month

February 2, 2022

by howtoexcel with No Comment conditional formatting google sheets investing stocks

Have you ever wondered how a stock has typically performed month over month? Using a spreadsheet, you can calculate monthly returns and identify patterns of which months are traditionally strong for a stock, and which ones aren’t. In this example, I’m going to use Google Sheets to pull in stock prices and calculate historical stock returns by month.

Start with pulling in historical stock prices

To get started, I’ll need to extract a stock’s price history. This can be done using Google Sheets’ GOOGLEFINANCE function. The key is in setting a start date that goes far enough back to ensure you get enough historical data to use in your calculations. A good function to use within that is the TODAY() function which ensures you will always be counting backward from today’s date and don’t need to hardcode a date. If I want to go back to 2008, for example, I can set my formula to deduct about 5,000 days.

To pull Amazon’s stock price going back that far, this is what my formula would look like in Google Sheets:

=GOOGLEFINANCE("AMZN","price",TODAY()-5000,TODAY())

Now I have the following values:

Amazon's historical stock price in Google Sheets.

The one problem here is that the date values contain the time, 16:00:00, which represents the 4 pm closing time of the stock market. I only want the actual date since I’m going to be doing a lookup and don’t want to include time. To extract just the date, I can use the DATE() function and use the YEAR(), MONTH() and DAY() functions to refer back to the values in column A. For example:

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

The above formula would give me the date in column A without the time. I’ll add an IF statement at the start so that in case the value in column A is blank, my formula simply won’t compute anything:

=if(A2="","",DATE(YEAR(A2),MONTH(A2),DAY(A2)))

Now I have a table that has just date values without any time:

Google Sheets table with dates showing no time next to share price.

Creating a date matrix

Next, what I’m going to do is create a matrix that has years going vertically and months going across:

A matrix in Google Sheets with months going across and years going down vertically.

I’m going to fill in these values with the stock’s returns in each of those months. The key to making this work is by using the DATEVALUE() function which allows me to enter a date. For example, if I entered the following formula:

=DATEVALUE("Jan 1, 2022")

It would result in the following output:

1/1/2022

In the first cell of my matrix, for the JAN 2021, I’ll combine the month abbreviation (JAN) with the year (2021) and the first day (1). Here’s how that would work if the month name is in cell F1 and the year is in E2:

=DATEVALUE(F$1&" 1, "&$E2)

However, let’s assume I don’t want to pull the first day of the month and instead want to pull the ending month’s value. For that, I can use the EOMONTH() function. And then I would enclose the current formula within that:

=EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0)

The 0 value at the end indicates how many months I want to jump by. And since I just want the end of the current month, I don’t need to jump by any months, which is why I set it to 0. At this point, I have a date, and now I can use this inside of a MATCH() function to find the row that matches this date. Assuming the date values in are column C, here is the formula:

=MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0),$C:$C,1)

And lastly, inside of an INDEX function that will return the corresponding price from column B:

=INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0),$C:$C,1),1)

Now the formula will pull in the price for a given month. But if I want the month-over-month return, I need to take the month-end price and divide it by the previous month’s ending value. To get the previous month’s price, I use the same formula except instead of a 0, I’ll enter -1 for the number of months:

=INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),-1),$C:$C,1),1)

I’ll combine the two formulas now, taking the current month-end price and dividing it by the previous month’s value and also deduct -1 at the end to adjust for it being a percent-change calculation:

=INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0),$C:$C,1),1)/INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),-1),$C:$C,1),1)-1

Now, copying this formula across the entire matrix, I can see the stock’s historical returns by month. I’ve added some conditional formatting to contrast the good months from the bad ones:

Matrix showing monthly returns while also utilizing conditional formatting.

Besides relying on colors, I can also add a win rate % where I can count the times where the return was more than 0% (i.e. a ‘win’) and divide this by the total number of values. In column F, for January, the formula looks like this:

=COUNTIF(F2:F12,">0")/COUNTA(F2:F12)

I’ll also average the returns so that it’s easier to see the best and worst-performing months:

Matrix showing monthly returns, summarized by win rates and averages.

Judging from this, April looks to be the best time to own Amazon’s stock. It normally returns a positive return and its average over the past 11 years has been a gain of just under 8.5%. To re-create this analysis for other stocks, simply change the ticker symbol.

If you liked this post on How to Calculate Historical Stock Returns by Month, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Create a MACD Chart

January 18, 2022

by howtoexcel with No Comment charts investing stocks

The MACD line and chart is a popular tool for technical analysts who buy and sell stocks. And in this post, I’ll show you how you can create it from start to finish. In my example, I’ve downloaded Apple’s stock price history for the past year from Yahoo Finance, and I’ll use that to calculate its MACD line. Here’s a sample of what I’m starting with:

Calculating the exponential moving averages

To calculate the MACD line, I’ll need to create multiple exponential moving averages (EMAs). One for 9 days, 12 days, and for 26 days. The logic will be the same so I can start with creating a formula for the 9-day EMA and then apply that to the others.

I’m going to create a couple of variables. The first being the n for the number of days. And the second one is for the weighting that you’ll apply to more recent values, and thus, turning it from a simple moving average into an exponential one. The weighting is calculated as follows:

=2/(1+n)

I’ll start my formulas to calculate the 9-day EMA by first checking to see if I have at least 10 data points. If I don’t, then I’m only calculating a simple moving average. Here’s how the start of that formula looks, assuming my closing stock prices start from cell B5 and my variable n is in cell C1:

IF(COUNTA($B$5:$B5)<=C$1,AVERAGE($B$5:$B5)

A key part of the formula is freezing cells properly. Cell $B$5 won’t move, but $B5 will as I drag it down. And this allows me to calculate the cumulative number of data points, and the corresponding average. The next part of the formula is what happens if I have more than nine data points. In that case, I will take the weighting (this is cell C2) on my sheet, and multiply that by the difference between the most recent stock price and the previous EMA. This will then get added to the previous day’s EMA:

C$2*($B5-$C4)+$C4

Column C is the one that contains the EMAs. My complete formula is as follows:

=IF(COUNTA($B$5:$B5)<=C$1,AVERAGE($B$5:$B5),C$2*($B5-$C4)+$C4)

I can now copy this logic across multiple columns to calculate the 12 and 26 day EMAs as well:

Multiple exponential moving averages calculated in Excel.

Now, I’ll set up the calculations for the final three columns:

MACD: This involves taking the 12-day EMA and subtracting the 26-day EMA from that.
Signal Line: This is a 9-day EMA of the MACD line.
Histogram: This is calculated as the difference between the MACD line and the Signal line.

With all those columns set up, here is my completed table:

Excel spreadsheet showing MACD calculations.

Creating the charts

With all the columns set up, the next part is to put the key data into a chart to illustrate the MACD line, Signal line, and Histogram. To make the chart look like a typical MACD chart, I’ll need to set the MACD line and Signal line to be line charts, and for the Histogram to be a column chart.

Initially, when the chart is created, there’s too much data in there since the data set is bigger than it needs to be:

Chart showing all MACD calculation columns.

To fix this, I right-click on the chart and click Select Data. Then, I remove all the series except for the last three: MACD line, Signal line, and Histogram. My updated chart looks as follows:

Excel chart showing the MACD line, Signal line, and Histogram.

There are still a few more changes that I am going to make here. The first is to fix the axis, as there are gaps between the column charts. That’s because Excel is recognizing the axis as a date axis. And while that’s correct, that means there will be gaps since stocks don’t trade every day of the week, and thus, those gaps, are weekends. To fix this, right-click on the axis and click Format Axis. And then, change the Axis Type so that it is a Text axis:

And then, under the Labels section, I set the position so that it is Low and at the bottom of the chart. My updated chart looks a bit better:

The last change you may want to consider is adjusting the column chart gap, to shrink it so the chart looks more like a histogram. If you right-click on them and click Format Data Series, there’s an option to change the Gap Width. I find that setting this to 50% normally results in a good gap size::

MACD chart in Excel after making changes to the columns.

And now we’ve got a chart that resembles what you might find on major finance sites when looking at MACD. If you want to follow along with the sheet that I’ve created, you can download my MACD chart template here.

If you liked this post on How to Create a MACD Chart, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Calculate Correlations Between Stocks

January 14, 2022

by howtoexcel with No Comment correlations investing stocks

Correlations can be helpful in determining if there is a pattern or relationship between two sets of data. It can be useful when looking at stocks as those that are highly correlated may move together in the same direction (note: this doesn’t mean their returns will be the same). And if you want to diversify, that’s not what you’ll want to accomplish. Instead, negatively correlated investments or ones that aren’t correlated at all may be more preferable in that situation. Below, I’ll show you how you can easily calculate correlations between multiple stocks from data that you can download from a source like Yahoo Finance.

Downloading the data

The first thing that’s needed when running correlations is to download at least two sets of data. In this example, I’ll download data for five stocks: Pfizer, Procter & Gamble, Tesla, Exxon Mobil, and Alphabet. None of those stocks is terribly similar to one another so there should be some decent diversification there.

Below, I’ve downloaded all the closing prices from Yahoo Finance for all of 2021. Here’s what that data looks like:

Stock prices download from Yahoo finance for five stocks.

The key is you want to make sure that the data is the same; you don’t want to have one stock showing a price at a different date than the other. They all need the same baseline. And that’s what the date field serves to do here. It itself isn’t necessary for the correlation calculation, but it’s just there to ensure that when you’re downloading and matching up data, everything lines up correctly to the right date.

The CORREL() funciton is useful for doing just a quick correlation calculation

Using Excel’s CORREL function, you can quickly calculate the correlation between two stocks. Pfizer is in column B and Procter & Gamble is in column C. If I wanted to quickly calculate their price correlation, my formula would be as follows:

=CORREL(B:B,C:C)

It doesn’t matter which order the data is in but there are only two ranges that are used as arguments in this function. This formula tells me there is a 94% correlation between these two stocks, at least, over the past year. That’s incredibly high and it could be because these are two fairly safe, value-oriented investments. Now, I could repeat this process for the other stocks here but there’s a quicker way to do that.

Using the Data Analysis option

Excel has some built-in Add-ins that you can enable that can quickly do tasks like this for you. You can access the Excel Add-ins from the Developer tab or by going through File->Options->Add-ins->Excel Add-ins. Either approach will get you to the same place. And once you’re there, you just need to check off the option for the Analysis Toolpak:

Once enabled, you will see the Data Analysis option on the Data tab. Clicking on that will give you many different options, including to do a Correlation:

Selecting correlation from the Data Analysis options.

On the next screen, I’ll have the option to select an Input Range. Here, I can select more than just two columns:

After clicking OK, Excel generates the correlation matrix for me in a new tab, saving me the time of doing all the calculations myself:

The lowest correlation noted here is between Tesla and Exxon Mobil, while the highest is between Pfizer and Procter & Gamble.

If you liked this post on How to Calculate Correlations Between Stocks, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Track Warren Buffett’s Portfolio Using Power Query

December 4, 2021

by howtoexcel with No Comment investing stocks

Billionaire investor Warren Buffett is the CEO of Berkshire Hathaway. And what trades his company is making is always big news among his followers and fans. In this post, I’ll show you how you can use Excel, and specifically Power Query, to determine what the company’s holdings are as of their most recent quarter, and how much they’ve changed over time.

Start with getting the most recent 13f report

Berkshire Hathaway reports its holdings every quarter and you can find those filings on the SEC website. This link filters out the 13f filings for you already. I’m going to click on the Documents button of the most recent report:

On the next page, the key link to use is the html file for the Information Table:

When I click on it, the report itself looks like a convenient table format:

Berkshire Hathaway Form 13 information table.

It’s here that you can see the holdings as of the end of the period for Berkshire Hathaway. I’m not going to copy this into Excel but instead, I am going to use the link itself.

Setting up the Power Query link

Back in Excel, I’m going to set up a connection by going to the Data tab and clicking From Web in the Get & Transform Data section. This will prompt me for a URL, where I will enter the link from the SEC website.

Entering the 13f report link into Power Query.

Upon clicking OK, the Navigator box shows up, where I have multiple tables to choose from:

Selecting the correct table in an initial Power Query connection.

Table 2 is the one that I want as that is the table that has the company names listed and all the other holding details. Since I want to make changes to the data, I’ll click on Transform Data rather than just loading it directly into Excel. Here’s how the table looks:

Data in Power Query showing Berkshire Hathaway's most recent holdings.

There are a few things right off the bat that I’m going to fix here:

The headers are not at the top.
The holdings need to be grouped so that I see the total per company.

To reference the columns easier, it’s important that the headings are correct. First up, I’ll remove the first two rows by clicking on the Remove Rows option in the Home tab in Power Query:

Removing the top rows from a Power Query table.

Removing the first two will get the names closer to the top, but the header names remain generic:

To fix this, I’ll click the option to Use First Row as Headers:

Now the first issue is fixed:

Headers in Power Query after the first row being promoted.

To group the companies, I’ll use the Group By option in Power Query:

On the next screen, I’ll group by issuer (i.e. the name of the company’s shares that are held), set the new column so that it is called ‘Total Shares’, and sum the PRN Amount (shares held):

Setting the group by parameters in Power Query.

Now I get a summary of all the shares that are held when grouped by company:

Now I can load this into Excel and have a list of the most recent Berkshire Hathaway holdings.

Comparing to a previous period

Next, I’ll compare the change in position from a previous period. Let’s say I want to go back a year. I can copy this query and just change the source so that it looks at this link from a year ago.

I’ve re-named the queries 2021-09-30 and 2020-09-30 (you can do this simply by right-clicking on the query name). To compare the two periods, what I’m going to do is merge the two queries. In the Merge options, make sure to select a Full Outer join so that all the rows are included. This ensures that you aren’t including just those companies that Berkshire Hathaway still owns shares of.

Next, I’ll expand the table to pull in the company name and the shares from the previous report:

Expanding the fields from a table in Power Query.

This will give me the same values from both tables. One of the things you’ll notice is because of the full outer join, some companies and share values show as null because they don’t exist on both reports:

Two merged queries in Power Query under a full outer join.

To fix this, I’m going to create a Conditional Column, which you can select from the Add Column tab. The rule I’m going to set up is as follows: if the value in one issuer/name column is null, take the other column’s value. Here’s how it looks:

Creating a conditional column in Power Query.

For the change in shares, I’ll need to do a calculation that takes the current holdings and subtracts the previous holdings. But before that, I need to convert the null values where the share numbers are to 0. To do this, select those columns and on the Home tab select the option to Replace Values

Now I can create a Custom Column to calculate the change in shares:

Creating a custom column to calculate the change in shares.

Here’s my updated table after removing the other columns:

Report in Power Query showing Berkshire Hathaway's change in holdings.

All that’s left is to load the data in Excel. Here’s what the end result looks like after applying some formatting and sorting the values in descending order:

Report in Power Query in Excel showing Berkshire Hathaway's change in holdings.

If you liked this post on How to Track Warren Buffett’s Portfolio Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Create Stock Charts in Excel Using Power Query

November 17, 2021

by howtoexcel with No Comment charts stocks

In Excel, there are multiple different stock charts you can create. All you need is some combination of the date, opening price, high price, low price, closing price, and volume to generate what you need. In this post, I’ll show you how you can utilize Power Query to pull that data in and transform it, without having to apply any manual changes to it every time. For an overview, you can check out this post on How to Get Stock Quotes From Yahoo Finance Using Power Query. I’m not going to repeat those steps and will assume that you’re familiar with that process.

In this example, I’ve downloaded the stock prices for Apple (NASDAQ:AAPL) for the month of October 2021:

This already has all the data that is needed to create the four types of stock charts in Excel:

High-Low-Close
Open-High-Low-Close
Volume-High-Low-Close
Volume-Open-High-Low-Close

The key to making these different charts is just ensuring that you’ve got the correct fields in your download, and in the right order. For the High-Low-Close chart, you only need three fields to generate the following chart:

If you’re creating the open-high-low-close chart, all you need is to add the open field to the data set:

And for the volume-high-low-close chart, it’s just the volume instead of the open that goes at the start, and then you get something that looks like this:

The last chart includes both the volume and the open before the high, low, and close values:

A volume open high low close chart in Excel.

These charts are fairly straightforward to generate once your data is in the right order. But rather than moving around different fields, you can make all the changes within Power Query so that right when your data loads, it’s in the correct format.

Using Power Query to adjust your download

To modify an existing query, go to the Data tab and select Queries & Connections. Off to the right, you should see your query, where you can right-click and Edit it:

The first thing I’ll edit is the date range. In my earlier post, I just downloaded the full year of data. But if I want to filter only for October, then I can click on the drop-down for the Date field and select Date Filters to filter Between a range of dates.

Using the date filter to filter dates in power query within a specified range.

Using the calendar icon, I can specify the range of dates I want to include in my download:

Filter rows in power query based on their date values.

Next, if I want to just include the data for the most basic chart, the high-low-close chart, I’ll right-click on the Open, Adj Close, and Volume columns to remove them. Then, I’m left with the following:

Power query stock download that only contains the high, low, and close fields.

Now, if I were to load the data in Excel, I would already have all the columns I need to create the chart:

Creating the high low close chart in Excel.

Note: if you want to get rid of the gap in dates on the chart, click on Format Axis and for the Axis Type, select Text axis:

Converting the date axis in Excel so that it reads as text instead of as a date.

This prevents Excel from trying to fill in any missing dates from your data set Another thing you may want to do is format the date field so that it is a custom format showing MMM DD so that it saves space:

Stock chart in Excel with custom date formatting.

Now, let’s go back into Power Query and this time create the more complex download, for the volume-open-high-low-close chart. I’ll start by removing the last step I applied which removed more columns than I need for this current download. To remove any steps in Power Query, click on the ‘X’ next to it:

In this example, it’s just the Removed Columns step I will eliminate. The only column I need to remove from the download this time around is the Adj Close. So that’s what I’ll do, right-click and remove that column. However, my table still is not in the correct order:

Power Query table after removing the Adj Close field.

The Volume column needs to go before the Open column. This is as easy as just dragging the column and putting it in front:

Power Query table after moving the Volume field.

Now that it’s in the right order, I can load and close this into Excel. And now, the volume-open-high-low-close chart can easily generate:

The volume open high low close chart in Excel.

Suppose I want to adjust my chart to include data from September as well. Again, I can go back to edit my query. This time, I’ll select the gear icon next to the Filtered Rows step:

Modifying a Power Query step using the gears icon.

There, I can just modify my date range using the calendar:

Modifying the date filter in Power Query.

Now, re-loading the data into Excel automatically updates my chart with a simple refresh:

The volume open high low close chart in Excel using a broader date range.

The beauty of this is this query will update with new data and your chart will also update, taking out the manual steps of having to make any changes yourself. And if you incorporate named ranges like in my post covering pulling stock prices, then the data will easily refresh based on the variables that you’ve entered.

If you liked this post on How to Create Stock Charts in Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.