No. of Recommendations: 0
Hey Fools,

My stock watchlist resides in Google Sheets. I like it a lot, but I'm trying to pull in data from Finviz.com to make it even more useful.

I'm made some progress, but I've encountered a problem and I'm currently stumped.

I'm trying to be able to sort my companies by growth rate. To do so, I first need to pull in data on what the companies' growth rate is.

I found some code that lets me pull in estimated EPS growth over the next five years from Finviz.

Here is that code(where "&B2" = the stock ticker):

=index(IMPORTHTML("http://finviz.com/quote.ashx?t="&B2,"table&quo..., 11),6,6)

In this example, the stock ticker that I'm looking at is PAYC. When I plug in this formula, I get the following result:

*21.51%*

This is almost exactly what I want because that is the estimated 5-year EPS growth rate for Paycom from this table:

https://finviz.com/quote.ashx?t=PAYC&ty=c&ta=1&p...

However, the problem I am facing now is that the format doesn't allow me to sort. Google Sheets doesn't see *21.51%* as a number. Instead, it sees it as just text.

What I would love to be able to do is turn *21.51%* into the clean number 21.51%.

I've tried =CLEAN() and =TRIM(), but those don't fix the problem.

Does anyone know what I can do (or another board that I can post this question to)?

Many thanks!

Brian
Print the post Back To Top
No. of Recommendations: 0
I'd try a REPLACE, replacing the '*' with nothing, nested within a VALUE. More details here ...
https://support.google.com/docs/table/25273?hl=en
Print the post Back To Top
No. of Recommendations: 0
Try copying all the cells with the *xx.xx%* format to a new area, and "paste special", specifying "Value".
Print the post Back To Top
No. of Recommendations: 1
What I would love to be able to do is turn *21.51%* into the clean number 21.51%.

I've tried =CLEAN() and =TRIM(), but those don't fix the problem.


I've run into a similar problem in Excel. You can probably solve it the same way I did.

Create a formula in another cell that does the clean/trim/whatever, referencing your downloaded text cell.

Sometimes the easiest thing to do is create an extra cell that does what you need, rather than trying to force the original cell to be what you want.
Print the post Back To Top
No. of Recommendations: 3
=value(SUBSTITUTE(A5,"*",""))
Print the post Back To Top
No. of Recommendations: 3
Thanks for your help here! I figured it out!

Here's what I did.

First, the IMPORTHMTL formula noted in my first post pulls in the data from Finviz. Here's what it looks like it (in text format):

*47.46*

Next, I created a new cell that chops off the extra two * using the REGEXEXTRACT formula and then convert it to a number using the VALUE foumula (AD2 = cell with the data):

=value(regexextract(AD2, "[0-9]*\.[0-9]+[0-9]+"))

The result is this:

47.46%

Voila!

Thanks for the suggestions!

Brian
Print the post Back To Top