Good stocks to invest – Updates to the Free Google Stock Portfolio Tracker



Good stocks to invest

good-stocks-to-invest-in

I created a Stock Portfolio Tracker 5 years ago that allows you to track your portfolio based on transactions and update their prices automatically. It has since become rather popular.

You can check out my current portfolio, using this portfolio tracker here.

To use it, you can tune in to the instructions here.

There have been some developments recently, and I would like to update the people who are supporting this Google Spreadsheet.


SGX Stopped Supporting Google Finance

The first thing is that SGX have stopped allowing investors to use GoogleFinance() to get update to date last trading price information of their stocks.

This affected many folks including myself who rely on GoogleFinance to pull latest stock prices.

This means that you either have to rely on manually entering the prices on a frequent basis, or rely on Yahoo Finance.

Problem with Price Retrieval of Yahoo Finance

Prices at Yahoo Finance does work, it is just that the ImportData() function in Google Spreadsheet is having an issue.

You will get a Result was not expanded automatically, please insert more rows and columns Error.

good-stocks-to-invest-in

This affects all the Yahoo Data Ref, Yahoo Data Ref USD, Yahoo Data Ref HKD sheets, which is where the stocks pull the prices from. I tried numerous ways and was not able to resolve it.

So I decided to put a different implementation.

I do not have time to think through this thoroughly due to some personal issues, but nevertheless this is my solution.

For those who are new to the Stock Portfolio Tracker, you can make a copy of the Google Spreadsheet to start using. It will work.

For those who are using the existing spreadsheet, here is some of my guide to how to get it working again.

1. Create functions to download Yahoo Data

Instead of using Yahoo Data Ref, we will now download price data for individual cells.

good-stocks-to-invest-in

In the code.gs text space, copy and paste the following:

function getYahooFinanceLastPrice(symbol) {
return UrlFetchApp.fetch(“http://download.finance.yahoo.com/d/quotes.csv?s=” + symbol + ‘&f=l1&p=.csv’).getContentText();
}

function getYahooFinance52WkLow(symbol) {
return UrlFetchApp.fetch(“http://download.finance.yahoo.com/d/quotes.csv?s=” + symbol + ‘&f=j&p=.csv’).getContentText();
}

function getYahooFinance52WkHigh(symbol) {
return UrlFetchApp.fetch(“http://download.finance.yahoo.com/d/quotes.csv?s=” + symbol + ‘&f=k&p=.csv’).getContentText();
}

function getYahooFinance50DMA(symbol) {
return UrlFetchApp.fetch(“http://download.finance.yahoo.com/d/quotes.csv?s=” + symbol + ‘&f=m3&p=.csv’).getContentText();
}

function getYahooFinance200DMA(symbol) {
return UrlFetchApp.fetch(“http://download.finance.yahoo.com/d/quotes.csv?s=” + symbol + ‘&f=m4&p=.csv’).getContentText();
}

good-stocks-to-invest-in

good-stocks-to-invest-in

Then click on the Disk icon to save the functions in the script and then click on the Run button.

2. Update and Propagate Yahoo Price with new Function

Once you have saved the script, and click Run, you will be able to use the functions.

Go to Stock Summary.

good-stocks-to-invest-in

At the first cell below the header, or cell G2, titled Yahoo Price, change the formula to the following:

=getYahooFinanceLastPrice(E2)*1

good-stocks-to-invest-in

Next Drag the bottom right blue box downwards to propagate the updated formula to all the rows.

Do this for all Stock Summary sheets.

2. Update and Propagate 52 Week High, Low, 50 Day MA and 200 Day MA with new Function

Next we will do the same for the 52 Week Low, 52 Week High, 50 Day MA and 200 Day MA.

good-stocks-to-invest-in

Update the cells with the following formula –

V2 (52 Wk Low):

=iferror(if(AND((I2getYahooFinance52WkLow(E2))/I2>Ref!$C$4,(I2getYahooFinance52WkLow(E2))/I2<Ref!$C$4),“Near”,if((I2getYahooFinance52WkLow(E2))/I2>0,“Above”,if((I2getYahooFinance52WkLow(E2))/I2<0,“Below”,“ERR”))),“ERR”)

W2 (52 Wk High):

=iferror(if(AND((I2getYahooFinance52WkHigh(E2))/I2>Ref!$C$4,(I2getYahooFinance52WkHigh(E2))/I2<Ref!$C$4),“Near”,if((I2getYahooFinance52WkHigh(E2))/I2>0,“Above”,if((I2getYahooFinance52WkHigh(E2))/I2<0,“Below”,“ERR”))),“ERR”)

X2 (50 Day MA)

=iferror(if(AND((I2getYahooFinance50DMA(E2))/I2>Ref!$C$4,(I2getYahooFinance50DMA(E2))/I2<Ref!$C$4),“Near”,if((I2getYahooFinance50DMA(E2))/I2>0,“Above”,if((I2getYahooFinance50DMA(E2))/I2<0,“Below”,“ERR”))),“ERR”)

Y2 (200 Day MA)

=iferror(if(AND((I2getYahooFinance200DMA(E2))/I2>Ref!$C$4,(I2getYahooFinance200DMA(E2))/I2<Ref!$C$4),“Near”,if((I2getYahooFinance200DMA(E2))/I2>0,“Above”,if((I2getYahooFinance200DMA(E2))/I2<0,“Below”,“ERR”))),“ERR”)

Next Drag the bottom right blue box downwards to propagate the updated formula to all the rows.

Do this for all Stock Summary sheets.

3. Delete Yahoo Data Ref, and similar sheets

Now that your Stock Summary sheets update the price themselves, there is no need for the Yahoo Data Ref sheets. You can remove them

Summary

Hope this solves your problem, and if you have a better implementation do let me know.

good-stocks-to-invest-in

– Good stocks to invest

Learn How To Be #1 on Google Results



Source link