Error while averaging values extracted from finviz

1
votes

So while building my dividend investing portfolio spreadsheet, I am extracting the annual Yield% values for respective stocks from the website finviz, and even though the value extraction works perfectly fine, the problem comes when I try to find the average of the cells that contain those values.

For example, in the table below: cell B2 extracts the Annual Dividend Yield% of VTI (which is in cell A2) by using the formula:

=IF(SUBSTITUTE(index(IMPORTHTML("http://finviz.com/quote.ashx?t="&REGEXEXTRACT(A2, "[^:]+$") ,"table", 9),8,2),"","")="-","", 
    SUBSTITUTE(index(IMPORTHTML("http://finviz.com/quote.ashx?t="&REGEXEXTRACT(A2, "[^:]+$") ,"table", 9),8,2),"",""))

I get

╔═══╦═══════════════╦════════════════════════╗
║   ║       A       ║           B            ║
╠═══╬═══════════════╬════════════════════════╣
║ 1 ║ Symbol        ║ Annual Dividend Yield% ║
║ 2 ║ NYSEARCA:VTI  ║ 1.72%                  ║
║ 3 ║ NYSEARCA:SPHD ║ 5.63%                  ║
║ 4 ║               ║ #DIV/0!                ║
╚═══╩═══════════════╩════════════════════════╝

Now when I try to find the average of the annual dividend yield% in Cell B4, using the formula:

=AVERAGE(B2:B3)

then I get this error of #DIV/0!
I am assuming it is because of me scraping the data from finviz and then trying to average, but not sure!

Regardless, is there a way to get around this and find the average% ?

1
google-sheetsgoogle-sheets-formula

1 Answers

2
votes

Your formula results are text including the * as well as the % symbols.
You need to change your formula so you get a number instead.

=IF(REGEXREPLACE(index(IMPORTHTML("http://finviz.com/quote.ashx?t="&REGEXEXTRACT(A2, "[^:]+$") ,"table", 9),8,2),"\*|%","")*1="-","", 
    REGEXREPLACE(index(IMPORTHTML("http://finviz.com/quote.ashx?t="&REGEXEXTRACT(A2, "[^:]+$") ,"table", 9),8,2),"\*|%","")*1)

What we actually do is change the SUBSTITUTE function to the REGEXREPLACE one and change the result from text to a number by using *1