3 Messages
Digest #3762
1b
Re: RCHGetTableCell - switching between quarterly and annual figures by "Randy Harmelink" rharmelink
2
Good news for option quotes users ... possible smfGetYahooPortfolioV by "Randy Harmelink" rharmelink
Messages
Fri Aug 5, 2016 3:11 am (PDT) . Posted by:
ricksbas
Hi Randy,
Thank you very much for your work on this wonderful application (that I unfortunately, only recently discovered).
I have been using the RCHGetTable function, but I cannot figure out how to switch between data pages where the website address itself does not change - i.e. like the financials in Yahoo Finance, switching between Quarterly and Annual Figures.
When I enter:
RCHGetTableCell("http://finance.yahoo.com/quote/BBBY/financials?p=BBBY",1,">Interest Expense"), for example - I only get the annual data.
Any suggestions would be appreciated. Thanks, Richard
Thank you very much for your work on this wonderful application (that I unfortunately, only recently discovered).
I have been using the RCHGetTable function, but I cannot figure out how to switch between data pages where the website address itself does not change - i.e. like the financials in Yahoo Finance, switching between Quarterly and Annual Figures.
When I enter:
RCHGetTableCell("http://finance.yahoo.com/quote/BBBY/financials?p=BBBY",1,"
Any suggestions would be appreciated. Thanks, Richard
Fri Aug 5, 2016 3:16 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
If you don't have a web page address with the data in the source code of
the web page, there's not much the add-in can do.
I looked at this for someone else the other day. It turns out they do a
query something like this:
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
KNEBV.HE?formatted=true&lang=en-US®ion=US&modules=incomeStatementHistory%
2CcashflowStatementHistory%2CbalanceSheetHistory%
2CincomeStatementHistoryQuarterly%2CcashflowStatementHistoryQuarterly%
2CbalanceSheetHistoryQuarterly%2Cearnings&corsDomain=finance.yahoo.com
...and then dynamically load the data as you switch. That means the other
data is never really within the source code of a web page that can be
extracted. The data can be extracted from query results like the above URL,
but it's pretty tough to do.
Have you tried the CSV file from MorningStar? It may be easier to grab the
whole financial statement with smfGetCSVFile() and then lookup what you
need from there. See the "FAQs and Tips" page for more specifics.
On Fri, Aug 5, 2016 at 2:09 AM, ricksbas@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I have been using the RCHGetTable function, but I cannot figure out how to
> switch between data pages where the website address itself does not change
> - i.e. like the financials in Yahoo Finance, switching between Quarterly
> and Annual Figures.
>
> When I enter:
>
> RCHGetTableCell("http://finance.yahoo.com/quote/BBBY/financials?p=BBBY
> ",1,">Interest Expense"), for example - I only get the annual data.
>
> Any suggestions would be appreciated. Thanks, Richard
>
the web page, there's not much the add-in can do.
I looked at this for someone else the other day. It turns out they do a
query something like this:
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
KNEBV.HE?formatted=true&lang=en-US®ion=US&modules=incomeStatementHistory%
2CcashflowStatementHistory%2CbalanceSheetHistory%
2CincomeStatementHistoryQuarterly%2CcashflowStatementHistoryQuarterly%
2CbalanceSheetHistoryQuarterly%2Cearnings&corsDomain=finance.yahoo.com
...and then dynamically load the data as you switch. That means the other
data is never really within the source code of a web page that can be
extracted. The data can be extracted from query results like the above URL,
but it's pretty tough to do.
Have you tried the CSV file from MorningStar? It may be easier to grab the
whole financial statement with smfGetCSVFile() and then lookup what you
need from there. See the "FAQs and Tips" page for more specifics.
On Fri, Aug 5, 2016 at 2:09 AM, ricksbas@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I have been using the RCHGetTable function, but I cannot figure out how to
> switch between data pages where the website address itself does not change
> - i.e. like the financials in Yahoo Finance, switching between Quarterly
> and Annual Figures.
>
> When I enter:
>
> RCHGetTableCell("http://finance.yahoo.com/quote/BBBY/financials?p=BBBY
> ",1,"
>
> Any suggestions would be appreciated. Thanks, Richard
>
Fri Aug 5, 2016 7:30 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I've been playing around with the portfolio views on Yahoo. Not only do
they support option quotes, but because of the streaming quotes feature,
they also provide real-time quotes for stocks.
I'm working on a function that can return columns of various views. If you
haven't created the Yahoo security cookie, it will just access the default
views. But if you have created the Yahoo security cookies, you can access
your own customized views. Unfortunately, this means different data items
can be there, so a function can't return specific data items. So I wrote a
function that could return specific columns. Which means any worksheet you
create might not work for someone else, unless they have customized their
Yahoo Portfolio view the same as yours.
In my testing, it appears the limit is somewhere around 450 ticker symbols
in a single table, but I was getting inconsistent results. That means you
can get multiple data items for multiple ticker symbols, all in one
Internet request. MUCH faster than grabbing stuff page by page for each
option ticker symbol.
Also, I was getting return results of all "Error" values from time to time,
but that appears to be a Yahoo problem. When I was looking at views with my
browser, occasionally a "Yahoo has encountered a problem" message would pop
up.
The yellow-highlighted items in the table below are manually entered by me,
and then the table was created by array-entering this formula:
=smfGetYahooPortfolioView(B4:B203,C2:V2,"v1",1)
....where:
* B4:B203 is the range my ticker symbols are in
* C2:V2 is the range my column requests are in
* "v1" is the "Basic" view, labeled by Yahoo as "v1". "fv" is a default
"Fundamentals" view. If you're logged in, you may have "v2" thru "v9" (not
sure how high they can go).
* 1 just says to give me the header row on the table. Handy for confirming
you're picking up the correct columns.
Anyway, as you can see, most option quotes are 15 minutes behind the stock
quotes, as they are still delayed.
1 2 3 4 7 10 11
Tickers Time
& Price Chg &
% Chg
Volume
Bid
Ask
MMM 09:46am EDT $178.29 $0.48 0.27% 103,633 $176.44 $179.36
ED 09:46am EDT $78.43 -$0.36 -0.46% 156,820 $0.00 $0.00
MCD 09:46am EDT $119.08 $0.78 0.66% 298,688 $118.54 $118.96
NWN 09:46am EDT $63.95 -$0.37 -0.58% 2,456 $32.21 $96.60
SPHD 09:42am EDT $39.36 $0.13 0.32% 27,802 $39.33 $39.36
COP 09:46am EDT $40.10 $0.00 0.00% 350,032 $40.24 $40.30
COP161118C00041000 Aug 4 $2.34 $0.00 0.00% 65 $2.32 $2.53
EMR 09:46am EDT $53.40 $0.37 0.69% 159,617 $0.00 $0.00
EMR160819C00052500 Aug 4
$0.99 $0.00 0.00% 140 $1.05 $1.20
MCD 09:46am EDT $119.08 $0.78 0.66% 298,688 $118.54 $118.96
MCD160902C00120000 Aug 4 $0.95 $0.00 0.00% 48 $0.00 $1.27
XOM 09:46am EDT $87.69 $0.21 0.24% 477,883 $87.47 $87.70
XOM160812C00086500 Aug 4 $1.21 $0.00 0.00% 185 $0.00 $0.00
WFC 09:46am EDT $48.39 $0.55 1.15% 2,756,197 $48.30 $48.31
WFC160805C00048000 09:30am EDT $0.43 $0.29 207.14% 221 $0.32 $0.42
WMT 09:46am EDT $73.88 $0.58 0.79% 457,766 $73.32 $73.46
WMT160812C00073000 09:30am EDT $0.70 $0.11 18.64% 3 $0.54 $0.85
SPY 09:46am EDT $217.56 $1.15 0.53% 9,859,248 $217.19 $217.20
IWM 09:46am EDT $121.37 $0.81 0.67% 2,369,418 $121.28 $121.29
COP160729C00043000 Error Error Error Error Error Error Error
In your browser, this would be what the "v1" view looks like for you:
https://finance.yahoo.com/quotes/MMM+0+ED+MCD+NWN+0+SPHD+0+COP+COP161118C00041000+0+EMR+EMR160819C00052500+0+MCD+MCD160902C00120000+0+XOM+XOM160812C00086500+0+WFC+WFC160805C00048000+0+WMT+WMT160812C00073000+0+SPY+IWM+0+COP160729C00043000+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0/view/v1?bypass=true<r=1
You could also grab it quick and dirty into EXCEL by pasting that URL into
a cell in EXCEL and referring to it with the RCHGetHTMLTable() function:
=RCHGetHTMLTable(B2,">Symbol")
I suppose these will go away with Verizon. :(
Questions? Comments? Anyone have any information on the Yahoo Portfolio
views that might be helpful?
they support option quotes, but because of the streaming quotes feature,
they also provide real-time quotes for stocks.
I'm working on a function that can return columns of various views. If you
haven't created the Yahoo security cookie, it will just access the default
views. But if you have created the Yahoo security cookies, you can access
your own customized views. Unfortunately, this means different data items
can be there, so a function can't return specific data items. So I wrote a
function that could return specific columns. Which means any worksheet you
create might not work for someone else, unless they have customized their
Yahoo Portfolio view the same as yours.
In my testing, it appears the limit is somewhere around 450 ticker symbols
in a single table, but I was getting inconsistent results. That means you
can get multiple data items for multiple ticker symbols, all in one
Internet request. MUCH faster than grabbing stuff page by page for each
option ticker symbol.
Also, I was getting return results of all "Error" values from time to time,
but that appears to be a Yahoo problem. When I was looking at views with my
browser, occasionally a "Yahoo has encountered a problem" message would pop
up.
The yellow-highlighted items in the table below are manually entered by me,
and then the table was created by array-entering this formula:
=smfGetYahooPortfolioView(B4:B203,C2:V2,"v1",1)
....where:
* B4:B203 is the range my ticker symbols are in
* C2:V2 is the range my column requests are in
* "v1" is the "Basic" view, labeled by Yahoo as "v1". "fv" is a default
"Fundamentals" view. If you're logged in, you may have "v2" thru "v9" (not
sure how high they can go).
* 1 just says to give me the header row on the table. Handy for confirming
you're picking up the correct columns.
Anyway, as you can see, most option quotes are 15 minutes behind the stock
quotes, as they are still delayed.
1 2 3 4 7 10 11
Tickers Time
& Price Chg &
% Chg
Volume
Bid
Ask
MMM 09:46am EDT $178.29 $0.48 0.27% 103,633 $176.44 $179.36
ED 09:46am EDT $78.43 -$0.36 -0.46% 156,820 $0.00 $0.00
MCD 09:46am EDT $119.08 $0.78 0.66% 298,688 $118.54 $118.96
NWN 09:46am EDT $63.95 -$0.37 -0.58% 2,456 $32.21 $96.60
SPHD 09:42am EDT $39.36 $0.13 0.32% 27,802 $39.33 $39.36
COP 09:46am EDT $40.10 $0.00 0.00% 350,032 $40.24 $40.30
COP161118C00041000 Aug 4 $2.34 $0.00 0.00% 65 $2.32 $2.53
EMR 09:46am EDT $53.40 $0.37 0.69% 159,617 $0.00 $0.00
EMR160819C00052500 Aug 4
$0.99 $0.00 0.00% 140 $1.05 $1.20
MCD 09:46am EDT $119.08 $0.78 0.66% 298,688 $118.54 $118.96
MCD160902C00120000 Aug 4 $0.95 $0.00 0.00% 48 $0.00 $1.27
XOM 09:46am EDT $87.69 $0.21 0.24% 477,883 $87.47 $87.70
XOM160812C00086500 Aug 4 $1.21 $0.00 0.00% 185 $0.00 $0.00
WFC 09:46am EDT $48.39 $0.55 1.15% 2,756,197 $48.30 $48.31
WFC160805C00048000 09:30am EDT $0.43 $0.29 207.14% 221 $0.32 $0.42
WMT 09:46am EDT $73.88 $0.58 0.79% 457,766 $73.32 $73.46
WMT160812C00073000 09:30am EDT $0.70 $0.11 18.64% 3 $0.54 $0.85
SPY 09:46am EDT $217.56 $1.15 0.53% 9,859,248 $217.19 $217.20
IWM 09:46am EDT $121.37 $0.81 0.67% 2,369,418 $121.28 $121.29
COP160729C00043000 Error Error Error Error Error Error Error
In your browser, this would be what the "v1" view looks like for you:
https://finance.yahoo.com/quotes/MMM+0+ED+MCD+NWN+0+SPHD+0+COP+COP161118C00041000+0+EMR+EMR160819C00052500+0+MCD+MCD160902C00120000+0+XOM+XOM160812C00086500+0+WFC+WFC160805C00048000+0+WMT+WMT160812C00073000+0+SPY+IWM+0+COP160729C00043000+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0+0/view/v1?bypass=true<r=1
You could also grab it quick and dirty into EXCEL by pasting that URL into
a cell in EXCEL and referring to it with the RCHGetHTMLTable(
=RCHGetHTMLTable(
I suppose these will go away with Verizon. :(
Questions? Comments? Anyone have any information on the Yahoo Portfolio
views that might be helpful?
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar