NoCode Solution to Track Stock Prices Using n8n and Google Sheets
Table of contents
- What is n8n?
- Why track stock prices?
- Outline of the project
- How to create the workflow in n8n?
- Activate the n8n-Google Sheets workflow
What is n8n?
n8n is a NoCode automation tool that can be easily customized to fit your specific needs. n8n also allows you to self-host and even add your own functions and apps. It is a great alternative to Zapier which is quite a popular automation platform.
Its node-based design makes it extremely versatile, allowing you to perform a variety of automation tasks by connecting a wide range of systems and applications. It is also a great alternative to Zapier, Make, and IFTTT which are also NoCode automation tools.
There is a version of n8n for Windows that you can try out on your systems. Otherwise, you can also install it on a cloud server and use it for free. You may check out this page for n8n setup.
Why track stock prices?
My objective is to track certain stocks that are approaching their long-term breakout prices and are expected to rise rapidly. Once they are close to their respective breakout regions, I'd like to invest in those stocks and hopefully make some profit.
A breakout is any price movement outside a defined support or resistance area. I am focusing on the price movement outside the long-term resistance area.
Of course, there are several tools and services in the market, some free and some paid, that allow you to track the stocks and set alerts as well. However, I still want to have my own stock alert setup that I can customize as per my requirements.
For example, I want to get notified over my e-mail when the price of a particular stock is within 2% (below or above) of the target price. Or let's say I want to be notified on my Telegram app as well. This is the kind of flexibility I need that can be easily achieved by n8n and Google Sheets.
However, you must have an API that should provide real-time prices of the stocks. Luckily I have found an API (https://rapidapi.com/suneetk92/api/latest-stock-price/) and it is also completely free.
Outline of the project
The image above has different types of n8n nodes connected in a linear manner. Once you have n8n installed at your end then these nodes along with many others will be available right out-of-the-box. Let me explain the workflow for this project.
Cron - It is a schedule trigger node that runs workflows at fixed intervals and times. This works in a similar way to the cron software utility in Unix-like systems. For this particular task, I have set the frequency to 5 minutes, so the entire workflow will get executed after every 5 minutes.
Get List of Stocks - This is a Google Sheets node. It allows you to read, write, update and delete data from your Google spreadsheet. So, there is a sheet in my Google Drive that contains a list of stock ticker symbols that I want to track. I am allowing this node to access that sheet and get the list of stocks.
Fetch Live Prices - The HTTP Request node allows you to make HTTP requests to query data from any app or service with a REST API. As discussed in the previous section, we are using a free API to fetch live stock details. You can use other APIs as well with this node, such as Alpha Vantage.
So, this node takes the list of stocks from the previous node and fetches stock details from the free API, for each stock from our list, in a JSON format.
Extract Prices - This node is called the Set node in n8n. The previous node, HTTP Request, provides stock data that includes open price, close price, volume, etc. Set node helps us in selecting the data of interest such as current price and stock symbol. The output of this node is a table of two columns that contain stock symbols and their current prices.
Update Sheet with New Prices - We can use the same node multiple times in a workflow in n8n. Here we are using the Google Sheets node again, but this time to update the stock prices in the same spreadsheet.
We will also perform some computations and data manipulation in our Google Sheets. However, most of the heavy loading has been done by n8n.
How to create the workflow in n8n?
We will start by creating a list of stocks in a Google spreadsheet. I will be working with the stocks of the National Stock Exchange (NSE), which is an Indian stock exchange.
As you can see below, I have shortlisted a few stocks that I believe are approaching their respective breakout regions.
At the moment there are 17 stocks on my list. I have not bought any of these stocks, but I will be tracking their prices using n8n in real-time and as soon as they are close to their breakout area, I may buy some stocks of these shares.
This is not a tested strategy for buying stocks, so I suggest you don't use it to buy or sell stocks. This tutorial is just for learning purposes and to show how we can use NoCode tools like n8n to track stocks.
If you can notice, I have also renamed the sheet as "StockLabels". This is important because we will be creating multiple sheets in this Google spreadsheet.
Now let's try to understand the workflow of n8n. I will try to explain the working of each node in detail. If you need any clarification then feel free to use the comments section.
1. Fetching the list of stock labels from Google Sheets
In n8n, when we connect two nodes then the output of the first node becomes the input for the next node. To configure any node in n8n, we just have to double-click it. Below you can see the configuration of the Cron node.
As per this configuration, this node will trigger the subsequent nodes at every five minutes. If you wish you can change this trigger time easily.
To add a new node, you can click on this plus button. It will open a side panel that contains all the nodes and you can even search the nodes.
The next node is a Google Sheets node. This node allows you to access Google Sheets in your Google Drive. However, you need to have OAuth2 credentials to allow n8n access to the Google Sheets. Refer to this link for more help.
Let me explain the configuration of the Google Sheets node.
In the screenshot above, OAuth2 authentification is specified in the first field, Authentication. Once you have received your OAuth2 credentials from Google, save them somewhere securely. You can use these credentials to access any Google Sheet in your Google Drive.
In the second field, Credential for Google Sheets OAuth2 API, I entered my Google credentials by clicking on the pen icon and saved this profile as "Google Sheets account 2".
Then we have to specify "sheet" as the resource and "Read" as the operation as we will read some data from a Google spreadsheet.
To get the spreadsheet ID you will have to open your Google Sheet which contains the list of stocks and check its URL.
The highlighted part of the URL is your spreadsheet ID, i.e., the text between "d/" and "/edit". Simply copy it and paste it into the configuration of the Google Sheets node in n8n.
In the next field of Range, you can specify the range of columns. But I have made one change here by adding the name of the sheet followed by "!". This trick is needed if you want to use multiple sheets from the same Google spreadsheet. The rest of the fields can be left untouched.
In n8n, you can easily test any node by clicking on the "Execute node" button. Let me click this button and see what happens.
As can be seen in the image above, our node fetched all the stock symbols from the Google sheet. This list will be used as the input for the next node in the workflow.
2. Get the latest stock details using an API
As mentioned earlier in the articles, we are using a free API (https://rapidapi.com/suneetk92/api/latest-stock-price/) to fetch the latest NSE stock prices. To use this API we have added the HTTP Request node to the Google Sheets node as shown below.
After fetching the list of stocks from the Google Sheets node, we can now use the HTTP Request node to send a GET request for each stock in our list. The API will return the latest information about the requested stock that will also contain the price of the stock.
Let's talk about the configuration of the HTTP Request node to understand it better.
To open this configuration you can double-click the node. On the input side, we have our list of stock symbols that has been provided by the previous node (Google Sheets node).
The first field is Authentication that you can leave as None since the stocks' API does not need any user authentication.
In the next field of "Request Method" we have to select the type of API request. Since we have to get some information on stocks from an API, therefore we have specified GET method.
After that, we have the URL field. Here we have to specify the endpoints for each stock in the input list. To create an endpoint for each stock click on the gear & cogs icon and then click on "Add Expression".
In the Expression section, we have the endpoint URL. Keep your cursor just after "Identifier=" and then on the Variable Selector panel select Name by expanding Current Node, Input Data, and JSON.
So, now for each stock symbol, the endpoint will automatically get updated. Then you can further update the remaining fields in the configuration.
If you scroll down a bit, you will find an option to add headers. As per the requirements of the NSE API that we are using, we have to use a couple of headers. In n8n, you can add any number of headers by clicking on "Add Header"
These header details you will get at RapidAPI after subscribing to the NSE API. Now let's hit the "Execute node" button to check the output of this node.
It turns out that the output contains 17 items which is equal to the number of stocks in the input list. So, we have got output for all the stocks.
Each item in the output is a list of information, such as symbol, open price, closing price, last price, volume, etc., related to the respective stocks.
From this information, we only need the value of "lastPrice" for all the stocks. We will do this job with the help of the Set node by appending it to the current HTTP Request node.
3. Extract the prices of the stock details
Now let's talk about the Set node. We will use this node in our workflow to select items of interest from the incoming data. This is how a Set node looks like 👇
Lets open the configuration form of the node. On the left panel you will see the input list of stock details.
In the form you can see that I have created two variables — Name and Price. Name variable will capture the stock symbols and Price variable will capture the prices. If you want you can create more varibles by clicking on "Add Value". For our task these two variables are enough.
Let's execute this node and verify the output.
The output contains a table of stock symbols and their latest prices. This is exactly what we wanted to extract. Now we have update these stock prices back into our Google spreadsheet.
4. Update Google Sheets with new stock prices
We will now add another Google Sheets node to our workflow as shown below.
In n8n, we can't update the same sheet in a Google spreadsheet from where we have fetched some data. Even if we try to update the same sheet, it will over write the existing data. So, to avoid this problem, we can simply create a new sheet in our Google spreadsheet.
Let's call the new sheet "StockPrices". Our n8n workflow will update sheets with the stock symbols along with their current prices.
Since the output of the Set node in our n8n workflow is a table whose headers are "Name" and "Price", so that's why I have added the same headers in this sheet. It will not work if the headers are do not match.
This is how the configuration is set up when we have to update a Google sheet. The OAuth2 credentials are same that we used earlier in the other Google Sheets node.
The Operation field this time contains "Create or Update". It will allow the node to add stock symbol and price pairs into the StockPrices sheet.
The Spreadsheet ID will remain the same. If you can recall, we extratced this ID from the URL of the Google spreadsheet for the other Google sheets node.
In the Range field, do not miss out to specify the sheet name before the columns range (StockPrices!A:F).
If you hit the "Execute node" button on this node it will update the Google sheet. So, let's avoid it and close this configuration form.
Activate the n8n-Google Sheets workflow
We can now activate the workflow so that it will fetch and update the stock prices at every 5 minutes. Below is the screenshot of the final workflow.
To execute the workflow once, you can click on the "Execute Workflow" button. However, to set it on auto pilot, we can simply toggle the Active switch at the top-right corner of the screen.
After a successful execution of this workflow, your Google sheet will be updated with the stock symbols and thier prices.
I suugest you create another sheet in the same Google spreadsheet if you want to perform any analysis using these numbers. However, you can append new stock symbols in the StockLabels sheet. The workflow will get the prices for the new stocks as well.
Feel free to connect with me if you need any help with n8n. Have a nice day! 👋👋