### Use SPARKLINE to create 52-week range price indicator chart for stocks in Google Sheets

The 52-week range price indicator chart shows the relative position of the current price compared to the 52-week low and the 52-week high price. It visualizes whether the current price is closer to the 52-week low or the 52-week high price. In this post, I explain how to create a 52-week range price indicator chart for stocks by using the SPARKLINE function and the GOOGLEFINANCE function in Google Sheets.

## Concept

With the GOOGLEFINANCE function, it's possible to retrieve the current price, the 52-week low price, and the 52-week high price of a stock by using the below formulas:

• `=GOOGLEFINANCE("AAPL")` returns the latest price of APPLE stock
• `=GOOGLEFINANCE("AAPL","low52")` returns the 52-week low price of APPLE stock
• `=GOOGLEFINANCE("AAPL","high52")` returns the 52-week high price of APPLE stock

To measure the relative position of the current price compared to the 52-week low price and 52-week high price, I compute a ratio that I call the 52-week range ratio. It is the result of dividing the difference between the current price and the 52-week low price by the difference between the 52-week high price and 52-week low price.

• `52-week range ratio = (current price - 52-week low price) / (52-week high price - 52-week low price)`
• The bigger this 52-week range ratio is, the closer to the 52-week high price the current price is. The max value for this 52-week range ratio is 1.
• The smaller this 52-week range ratio is, the closer to the 52-week low price the current price is. The min value for this 52-week range ratio is 0.

To visualize effectively the 52-week price range as a miniature chart within a single cell, I use the SPARKLINE function with the configurations as below:

`=SPARKLINE(G2,{"charttype","bar";"max",1})`

• I provide only the 52-week range ratio as data for the chart.
• I specify bar as the charttype option of the SPARKLINE function.
• I specify 1 for the max option of the SPARKLINE function.

As a result, the 52-week range price chart looks like a progress bar within a single cell.

• If the current price is close to the 52-week high, the 52-week range price chart is about fully filled with the color of choice.
• If the current price is close to the 52-week low, the 52-week range price chart is about empty.

## Demo

Demo spreadsheet: how to create a 52-week range price indicator chart for stocks by using the SPARKLINE function and the GOOGLEFINANCE function in Google Sheets

## Conclusion

In this post, I explained how to use the SPARKLINE function and the GOOGLEFINANCE function to create 52-week range price charts for stocks in Google Sheets. With the SPARKLINE function, I have made other charts to help watch the movement of stocks. I will write about them in future posts.

This post is part of a series of posts about effectively using the SPARKLINE function and the GOOGLEFINANCE function for managing a stock investment in Google Sheets.

## Disclaimer

The post is only for informational purposes and not for trading purposes or financial advice.

## Feedback

If you have any feedback, question, or request please:

## Support this blog

If you value my work, please support me with as little as a cup of coffee! I appreciate it. Thank you!

If you read it this far, I hope you have enjoyed the content of this post. If you like it, share it with your friends!

### Use SPARKLINE column chart to create price chart with reference price

I own and follow several stocks in my investment portfolio. I pick a reference price for each stock. To effectively track the movement of a stock, I need to visualize its 52-week prices based on the reference price that I determined. In this post, I explain how to do so with the SPARKLINE column chart in Google Sheets.

### Compute cost basis of stocks with FIFO method in Google Sheets

After selling a portion of my holdings in a stock, the cost basis for the remain shares of that stock in my portfolio is not simply the sum of all transactions. When selling, I need to decide which shares I want to sell. One of the most common accounting methods is FIFO (first in, first out), meaning that the shares I bought earliest will be the shares I sell first. As you might already know, I use Google Sheets extensively to manage my stock portfolio investment, but, at the moment of writing this post, I find that Google Sheets does not provide a built-in formula for FIFO. Luckily, with lots of effort, I succeeded in building my own FIFO solution in Google Sheets, and I want to share it on this blog. In this post, I explain how to implement FIFO method in Google Sheets to compute cost basis in stocks investing.

### Slice array in Google Sheets

Many functions in Google Sheets return an array as the result. However, I find that there is a lack of built-in support functions in Google Sheets when working with an array. For example, the GOOGLEFINANCE function can return the historical prices of a stock as a table of two columns and the first-row being headers Date and Close. How can I ignore the headers or remove the headers from the results?

### How to convert column index into letters with Google Apps Script

Although Google Sheets does not provide a ready-to-use function that takes a column index as an input and returns corresponding letters as output, we can still do the task by leveraging other built-in functions ADDRESS , REGEXEXTRACT , INDEX , SPLIT as shown in the post . However, in form of a formula, that solution is not applicable for scripting with Google Apps Script. In this post, we look at how to write a utility function with Google Apps Script that converts column index into corresponding letters.

### Create personal stock portfolio tracker with Google Sheets and Google Data Studio

I have been investing in the stock market for a while. I was looking for a software tool that could help me better manage my portfolio, but, could not find one that satisfied my needs. One day, I discovered that the Google Sheets application has a built-in function called GOOGLEFINANCE which fetches current or historical prices of stocks into spreadsheets. So I thought it is totally possible to build my own personal portfolio tracker with Google Sheets. I can register my transactions in a sheet and use the pivot table, built-in functions such as GOOGLEFINANCE, and Apps Script to automate the computation for daily evolutions of my portfolio as well as the current position for each stock in my portfolio. I then drew some sort of charts within the spreadsheet to have some visual ideas of my portfolio. However, I quickly found it inconvenient to have the charts overlapped the table and to switch back and forth among sheets in the spreadsheet. That's when I came to know the existen

Anyone using Google Sheets to manage stock portfolio investment must know how to use the GOOGLEFINANCE function to fetch historical prices of stocks. As I have used it extensively to manage my stock portfolio investment in Google Sheets , I have learned several best practices for using the GOOGLEFINANCE function that I would like to share in this post.

### Create a dividend income tracker with Google Sheets by simply using pivot tables

As my investment strategy is to buy stocks that pay regular and stable dividends during a long-term period, I need to monitor my dividends income by stocks, by months, and by years, so that I can answer quickly and exactly the following questions: How much dividend did I receive on a given month and a given year? How much dividend did I receive for a given stock in a given year? Have a given stock's annual dividend per share kept increasing gradually over years? Have a given stock's annual dividend yield been stable over years? In this post, I explain how to create a dividend tracker for a stock investment portfolio with Google Sheets by simply using pivot tables.

### How to use WEEKDAY function to get last Friday in Google Sheets

As I manage my stock investment portfolio in Google Sheets, I need to see its evolution over time, for example, in the last year. However, the computation for daily evolution is resource-consuming and might cause performance issues for the spreadsheet. As an alternative, I compute only the weekly evolution of the investment portfolio for the last year. For each week, I compute only the portfolio's value at the end of the Friday. For that, I need a Google Sheets formula to return the last Friday for a given date. This post explains how I do that with the WEEKDAY formula in Google Sheets.

### Manage Stock Transactions With Google Sheets

The first task of building a stock portfolio tracker is to design a solution to register transactions. A transaction is an event when change happens to a stock portfolio, for instance, selling shares of a company, depositing money, or receiving dividends. Transactions are essential inputs to a stock portfolio tracker and it is important to keep track of transactions to make good decisions in investment. In this post, I will explain step by step how to keep track of stock transactions with Google Sheets.