11 Messages
Digest #4615
Messages
Sat Sep 14, 2019 10:04 am (PDT) . Posted by:
amt2100
Hi Randy,
I'm looking to fill just a couple parameters on a watchlist of about 200 tickers. (I know about the 1000 retrievals per sheet limitation)
Just want to grab, say Forward and Trailing PE.
Whenever I use Gurufocus's website for retrievals, it bogs down and kills the spreadsheet with just pasting the formula in as little as 20 cells.
First question, what source is the fastest filling of data, without bogging down the spreadsheet?
I tried to utilize the templates on the ogres-crypt, Yahoo Key Statistics, seems very fast, but I don't understand how the formula works:
=RCHGetElementNumber(Ticker,943)
How does that instruction know what cell the Ticker is in? How would I adjust this for a list of different Tickers?
Assuming this is the most efficient formula to use.
Thanks Randy. Hope you're doing well.
Jim
I'm looking to fill just a couple parameters on a watchlist of about 200 tickers. (I know about the 1000 retrievals per sheet limitation)
Just want to grab, say Forward and Trailing PE.
Whenever I use Gurufocus's website for retrievals, it bogs down and kills the spreadsheet with just pasting the formula in as little as 20 cells.
First question, what source is the fastest filling of data, without bogging down the spreadsheet?
I tried to utilize the templates on the ogres-crypt, Yahoo Key Statistics, seems very fast, but I don't understand how the formula works:
=RCHGetElementNumbe
How does that instruction know what cell the Ticker is in? How would I adjust this for a list of different Tickers?
Assuming this is the most efficient formula to use.
Thanks Randy. Hope you're doing well.
Jim
Sat Sep 14, 2019 12:28 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
If you have a large number of ticker symbols, you'll want to stay away from
RCHGetElementNumber(). It basically has to retrieve a new web page for each
ticker symbol. That takes a lot of time, and is not what the add-in was
designed for.
In the example, you gave, "Ticker" is a range name, so it refers to a cell.
That's where it's getting the ticker from. In most templates, that's a
fixed cell. But a ranged name can also be a relative reference. Or, you
could just use a direct cell reference.
Forward and trailing PE are both available with smfGetYahooPortfolioView(),
so you could get them both for 200 tickers with a single Internet request,
as long as you array-entered a single function over a range.
Another option would be to go to a screener, manually run the screen, then
export the results. Then just do EXCEL VLOOKUP() functions to extract the
data. I used to do this with the Zack's screener, since I could export the
entire stock market at once. It's just a matter of defining your screening
criteria and view of the data. That would all be done independently of the
add-in.
When I expect to get data for a small (and I stress small) universe of
stocks, I often use the smfUpdateDownloadTable process, because it loads
values into a table based on formulas. That means the formulas don't need
to recalculate every time you open the workbook. You can update rows, or
columns, or ranges of values within the table, as you need, under your
control. I do that for a dividends worksheet I have. Because dividends are
paid out quarterly, I don't need to update all ticker symbols each time I
open the worksheet. Just those that I know might have changed, based on the
dates I have in the table. Then, I can have my other workbooks refer to
that dividend data, instead of needing to go back to the Internet each
time. But it does mean you have a defined universe of stocks.
On Sat, Sep 14, 2019 at 10:04 AM amt2100@... wrote:
>
>
> I'm looking to fill just a couple parameters on a watchlist of about 200
> tickers. (I know about the 1000 retrievals per sheet limitation)
>
> Just want to grab, say Forward and Trailing PE.
>
> Whenever I use Gurufocus's website for retrievals, it bogs down and kills
> the spreadsheet with just pasting the formula in as little as 20 cells..
>
>
>
> First question, what source is the fastest filling of data, without
> bogging down the spreadsheet?
>
>
>
> I tried to utilize the templates on the ogres-crypt, Yahoo Key Statistics,
> seems very fast, but I don't understand how the formula works:
>
> =RCHGetElementNumber(Ticker,943)
>
>
>
> How does that instruction know what cell the Ticker is in? How would I
> adjust this for a list of different Tickers?
>
> Assuming this is the most efficient formula to use.
>
>
>
>
>
RCHGetElementNumber
ticker symbol. That takes a lot of time, and is not what the add-in was
designed for.
In the example, you gave, "Ticker" is a range name, so it refers to a cell.
That's where it's getting the ticker from. In most templates, that's a
fixed cell. But a ranged name can also be a relative reference. Or, you
could just use a direct cell reference.
Forward and trailing PE are both available with smfGetYahooPortfoli
so you could get them both for 200 tickers with a single Internet request,
as long as you array-entered a single function over a range.
Another option would be to go to a screener, manually run the screen, then
export the results. Then just do EXCEL VLOOKUP() functions to extract the
data. I used to do this with the Zack's screener, since I could export the
entire stock market at once. It's just a matter of defining your screening
criteria and view of the data. That would all be done independently of the
add-in.
When I expect to get data for a small (and I stress small) universe of
stocks, I often use the smfUpdateDownloadTa
values into a table based on formulas. That means the formulas don't need
to recalculate every time you open the workbook. You can update rows, or
columns, or ranges of values within the table, as you need, under your
control. I do that for a dividends worksheet I have. Because dividends are
paid out quarterly, I don't need to update all ticker symbols each time I
open the worksheet. Just those that I know might have changed, based on the
dates I have in the table. Then, I can have my other workbooks refer to
that dividend data, instead of needing to go back to the Internet each
time. But it does mean you have a defined universe of stocks.
On Sat, Sep 14, 2019 at 10:04 AM amt2100@... wrote:
>
>
> I'm looking to fill just a couple parameters on a watchlist of about 200
> tickers. (I know about the 1000 retrievals per sheet limitation)
>
> Just want to grab, say Forward and Trailing PE.
>
> Whenever I use Gurufocus's website for retrievals, it bogs down and kills
> the spreadsheet with just pasting the formula in as little as 20 cells..
>
>
>
> First question, what source is the fastest filling of data, without
> bogging down the spreadsheet?
>
>
>
> I tried to utilize the templates on the ogres-crypt, Yahoo Key Statistics,
> seems very fast, but I don't understand how the formula works:
>
> =RCHGetElementNumbe
>
>
>
> How does that instruction know what cell the Ticker is in? How would I
> adjust this for a list of different Tickers?
>
> Assuming this is the most efficient formula to use.
>
>
>
>
>
Sat Sep 14, 2019 1:46 pm (PDT) . Posted by:
amt2100
Thanks Randy. This is all very good advice.
Could I ask, what would be the formula for PE, for the cell, using the smfGetYahooPortfolioView? Is there a list of the items that you can pull using this?
Thanks again,
Jim
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 3:28 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
If you have a large number of ticker symbols, you'll want to stay away from RCHGetElementNumber(). It basically has to retrieve a new web page for each ticker symbol. That takes a lot of time, and is not what the add-in was designed for.
In the example, you gave, "Ticker" is a range name, so it refers to a cell. That's where it's getting the ticker from. In most templates, that's a fixed cell. But a ranged name can also be a relative reference. Or, you could just use a direct cell reference.
Forward and trailing PE are both available with smfGetYahooPortfolioView(), so you could get them both for 200 tickers with a single Internet request, as long as you array-entered a single function over a range.
Another option would be to go to a screener, manually run the screen, then export the results. Then just do EXCEL VLOOKUP() functions to extract the data. I used to do this with the Zack's screener, since I could export the entire stock market at once. It's just a matter of defining your screening criteria and view of the data. That would all be done independently of the add-in.
When I expect to get data for a small (and I stress small) universe of stocks, I often use the smfUpdateDownloadTable process, because it loads values into a table based on formulas. That means the formulas don't need to recalculate every time you open the workbook. You can update rows, or columns, or ranges of values within the table, as you need, under your control. I do that for a dividends worksheet I have. Because dividends are paid out quarterly, I don't need to update all ticker symbols each time I open the worksheet. Just those that I know might have changed, based on the dates I have in the table. Then, I can have my other workbooks refer to that dividend data, instead of needing to go back to the Internet each time. But it does mean you have a defined universe of stocks.
On Sat, Sep 14, 2019 at 10:04 AM amt2100@... wrote:
I'm looking to fill just a couple parameters on a watchlist of about 200 tickers. (I know about the 1000 retrievals per sheet limitation)
Just want to grab, say Forward and Trailing PE.
Whenever I use Gurufocus's website for retrievals, it bogs down and kills the spreadsheet with just pasting the formula in as little as 20 cells.
First question, what source is the fastest filling of data, without bogging down the spreadsheet?
I tried to utilize the templates on the ogres-crypt, Yahoo Key Statistics, seems very fast, but I don't understand how the formula works:
=RCHGetElementNumber(Ticker,943)
How does that instruction know what cell the Ticker is in? How would I adjust this for a list of different Tickers?
Assuming this is the most efficient formula to use.
Could I ask, what would be the formula for PE, for the cell, using the smfGetYahooPortfolioView? Is there a list of the items that you can pull using this?
Thanks again,
Jim
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 3:28 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
If you have a large number of ticker symbols, you'll want to stay away from RCHGetElementNumber
In the example, you gave, "Ticker" is a range name, so it refers to a cell. That's where it's getting the ticker from. In most templates, that's a fixed cell. But a ranged name can also be a relative reference. Or, you could just use a direct cell reference.
Forward and trailing PE are both available with smfGetYahooPortfoli
Another option would be to go to a screener, manually run the screen, then export the results. Then just do EXCEL VLOOKUP() functions to extract the data. I used to do this with the Zack's screener, since I could export the entire stock market at once. It's just a matter of defining your screening criteria and view of the data. That would all be done independently of the add-in.
When I expect to get data for a small (and I stress small) universe of stocks, I often use the smfUpdateDownloadTa
On Sat, Sep 14, 2019 at 10:04 AM amt2100@... wrote:
I'm looking to fill just a couple parameters on a watchlist of about 200 tickers. (I know about the 1000 retrievals per sheet limitation)
Just want to grab, say Forward and Trailing PE.
Whenever I use Gurufocus's website for retrievals, it bogs down and kills the spreadsheet with just pasting the formula in as little as 20 cells.
First question, what source is the fastest filling of data, without bogging down the spreadsheet?
I tried to utilize the templates on the ogres-crypt, Yahoo Key Statistics, seems very fast, but I don't understand how the formula works:
=RCHGetElementNumbe
How does that instruction know what cell the Ticker is in? How would I adjust this for a list of different Tickers?
Assuming this is the most efficient formula to use.
Sat Sep 14, 2019 1:49 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Check the template...all the fields are documented there.
On Sat, Sep 14, 2019 at 1:46 PM amt2100@... wrote:
>
> Could I ask, what would be the formula for PE, for the cell, using the smfGetYahooPortfolioView?
> Is there a list of the items that you can pull using this?
>
>
>
>
>
On Sat, Sep 14, 2019 at 1:46 PM amt2100@... wrote:
>
> Could I ask, what would be the formula for PE, for the cell, using the smfGetYahooPortfoli
> Is there a list of the items that you can pull using this?
>
>
>
>
>
Sat Sep 14, 2019 2:53 pm (PDT) . Posted by:
amt2100
Sorry Randy. I can't get this formula to enter as an array. Tried copying/pasting, CTRL/SHFT/ENTER, etc.
You mind a little assistance?
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 4:49 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
Check the template...all the fields are documented there.
On Sat, Sep 14, 2019 at 1:46 PM amt2100@... wrote:
Could I ask, what would be the formula for PE, for the cell, using the smfGetYahooPortfolioView? Is there a list of the items that you can pull using this?
You mind a little assistance?
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 4:49 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
Check the template...all the fields are documented there.
On Sat, Sep 14, 2019 at 1:46 PM amt2100@... wrote:
Could I ask, what would be the formula for PE, for the cell, using the smfGetYahooPortfoli
Sat Sep 14, 2019 4:58 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
1. Select the entire range first. Say, D3:I411?
2. Type in the formula
3. Press Ctrl+Shift+Enter to array-enter the formula over the range..
The Ctrl+Shift+Enter is hold down both the Ctrl and Shift key, then hit
Enter.
On Sat, Sep 14, 2019 at 2:53 PM amt2100@... wrote:
>
> Sorry Randy. I can't get this formula to enter as an array. Tried
> copying/pasting, CTRL/SHFT/ENTER, etc.
>
> You mind a little assistance?
>
>
>
>
>
2. Type in the formula
3. Press Ctrl+Shift+Enter to array-enter the formula over the range..
The Ctrl+Shift+Enter is hold down both the Ctrl and Shift key, then hit
Enter.
On Sat, Sep 14, 2019 at 2:53 PM amt2100@... wrote:
>
> Sorry Randy. I can't get this formula to enter as an array. Tried
> copying/pasting, CTRL/SHFT/ENTER, etc.
>
> You mind a little assistance?
>
>
>
>
>
Sat Sep 14, 2019 5:32 pm (PDT) . Posted by:
amt2100
I thought that's what I was doing but I copied and pasted it instead of typing it. It started working now, but it's not working correctly.
The results are going into the row below the row that the ticker is on. And I'm still getting the P/E, EPS TTM and Forward P/E for the first ticker, ESRX.
Very strange. Any ideas?
So close!
Thanks Randy.
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 7:58 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
1. Select the entire range first. Say, D3:I411?
2. Type in the formula
3. Press Ctrl+Shift+Enter to array-enter the formula over the range..
The Ctrl+Shift+Enter is hold down both the Ctrl and Shift key, then hit Enter.
On Sat, Sep 14, 2019 at 2:53 PM amt2100@... wrote:
Sorry Randy. I can't get this formula to enter as an array. Tried copying/pasting, CTRL/SHFT/ENTER, etc.
You mind a little assistance?
The results are going into the row below the row that the ticker is on. And I'm still getting the P/E, EPS TTM and Forward P/E for the first ticker, ESRX.
Very strange. Any ideas?
So close!
Thanks Randy.
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 7:58 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
1. Select the entire range first. Say, D3:I411?
2. Type in the formula
3. Press Ctrl+Shift+Enter to array-enter the formula over the range..
The Ctrl+Shift+Enter is hold down both the Ctrl and Shift key, then hit Enter.
On Sat, Sep 14, 2019 at 2:53 PM amt2100@... wrote:
Sorry Randy. I can't get this formula to enter as an array. Tried copying/pasting, CTRL/SHFT/ENTER, etc.
You mind a little assistance?
Sat Sep 14, 2019 5:39 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You have to remember you are doing a header row, so the array-entered range
will need to have one more row than the number of tickers, and start one
row higher if you want the ticker rows to match up.
That's why I said to select your range starting from D3, when your first
ticker symbol was in A4.
On Sat, Sep 14, 2019 at 5:32 PM amt2100@... wrote:
>
> I thought that's what I was doing but I copied and pasted it instead of
> typing it. It started working now, but it's not working correctly.
>
> The results are going into the row below the row that the ticker is on..
> And I'm still getting the P/E, EPS TTM and Forward P/E for the first
> ticker, ESRX.
>
> Very strange. Any ideas?
>
> So close!
>
> Thanks Randy.
>
>
>
>
>
>
>
will need to have one more row than the number of tickers, and start one
row higher if you want the ticker rows to match up.
That's why I said to select your range starting from D3, when your first
ticker symbol was in A4.
On Sat, Sep 14, 2019 at 5:32 PM amt2100@... wrote:
>
> I thought that's what I was doing but I copied and pasted it instead of
> typing it. It started working now, but it's not working correctly.
>
> The results are going into the row below the row that the ticker is on..
> And I'm still getting the P/E, EPS TTM and Forward P/E for the first
> ticker, ESRX.
>
> Very strange. Any ideas?
>
> So close!
>
> Thanks Randy.
>
>
>
>
>
>
>
Sat Sep 14, 2019 6:11 pm (PDT) . Posted by:
amt2100
That did it. I missed that nuance.
So it's looking good and working for most tickers. Any idea why I would have a bunch of tickers that show two dashes, even though the data is readily available, i.e. it has a PE, etc.?
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 8:40 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
You have to remember you are doing a header row, so the array-entered range will need to have one more row than the number of tickers, and start one row higher if you want the ticker rows to match up.
That's why I said to select your range starting from D3, when your first ticker symbol was in A4.
On Sat, Sep 14, 2019 at 5:32 PM amt2100@... wrote:
I thought that's what I was doing but I copied and pasted it instead of typing it. It started working now, but it's not working correctly.
The results are going into the row below the row that the ticker is on. And I'm still getting the P/E, EPS TTM and Forward P/E for the first ticker, ESRX.
Very strange. Any ideas?
So close!
Thanks Randy.
So it's looking good and working for most tickers. Any idea why I would have a bunch of tickers that show two dashes, even though the data is readily available, i.e. it has a PE, etc.?
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 8:40 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
You have to remember you are doing a header row, so the array-entered range will need to have one more row than the number of tickers, and start one row higher if you want the ticker rows to match up.
That's why I said to select your range starting from D3, when your first ticker symbol was in A4.
On Sat, Sep 14, 2019 at 5:32 PM amt2100@... wrote:
I thought that's what I was doing but I copied and pasted it instead of typing it. It started working now, but it's not working correctly.
The results are going into the row below the row that the ticker is on. And I'm still getting the P/E, EPS TTM and Forward P/E for the first ticker, ESRX.
Very strange. Any ideas?
So close!
Thanks Randy.
Sat Sep 14, 2019 6:36 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The question would be what data Yahoo has in their portfolio tool, not what
they display on the regular web page. There are differences. I'm not sure
if it's different providers of the data, or just using databases
differently.
Have you checked, though? It appears to me ESRX and ILG haven't traded
since June. And CTIC says N/A on its quotes page.
Or maybe Yahoo is having issues this weekend?
On Sat, Sep 14, 2019 at 6:11 PM amt2100@... wrote:
>
> That did it. I missed that nuance.
>
> So it's looking good and working for most tickers. Any idea why I would
> have a bunch of tickers that show two dashes, even though the data is
> readily available, i.e. it has a PE, etc.?
>
>
>
>
>
>
>
they display on the regular web page. There are differences. I'm not sure
if it's different providers of the data, or just using databases
differently.
Have you checked, though? It appears to me ESRX and ILG haven't traded
since June. And CTIC says N/A on its quotes page.
Or maybe Yahoo is having issues this weekend?
On Sat, Sep 14, 2019 at 6:11 PM amt2100@... wrote:
>
> That did it. I missed that nuance.
>
> So it's looking good and working for most tickers. Any idea why I would
> have a bunch of tickers that show two dashes, even though the data is
> readily available, i.e. it has a PE, etc.?
>
>
>
>
>
>
>
Sat Sep 14, 2019 9:10 pm (PDT) . Posted by:
amt2100
Thanks again Randy.
Your help is invaluable!
Best regards,
Jim
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 9:36 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
The question would be what data Yahoo has in their portfolio tool, not what they display on the regular web page. There are differences. I'm not sure if it's different providers of the data, or just using databases differently.
Have you checked, though? It appears to me ESRX and ILG haven't traded since June. And CTIC says N/A on its quotes page.
Or maybe Yahoo is having issues this weekend?
On Sat, Sep 14, 2019 at 6:11 PM amt2100@... wrote:
That did it. I missed that nuance.
So it's looking good and working for most tickers. Any idea why I would have a bunch of tickers that show two dashes, even though the data is readily available, i.e. it has a PE, etc.?
Your help is invaluable!
Best regards,
Jim
From: smf_addin@yahoogroups.com <smf_addin@yahoogroups.com>
Sent: Saturday, September 14, 2019 9:36 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Question about quickest access to data
The question would be what data Yahoo has in their portfolio tool, not what they display on the regular web page. There are differences. I'm not sure if it's different providers of the data, or just using databases differently.
Have you checked, though? It appears to me ESRX and ILG haven't traded since June. And CTIC says N/A on its quotes page.
Or maybe Yahoo is having issues this weekend?
On Sat, Sep 14, 2019 at 6:11 PM amt2100@... wrote:
That did it. I missed that nuance.
So it's looking good and working for most tickers. Any idea why I would have a bunch of tickers that show two dashes, even though the data is readily available, i.e. it has a PE, etc.?
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar