15 Messages
Digest #3768
1b
Re: Good news for option quotes users ... possible smfGetYahooPortfo by "Randy Harmelink" rharmelink
1d
Re: Good news for option quotes users ... possible smfGetYahooPortfo by "Randy Harmelink" rharmelink
1f
Re: Good news for option quotes users ... possible smfGetYahooPortfo by "Randy Harmelink" rharmelink
1g
Re: Good news for option quotes users ... possible smfGetYahooPortfo by "Randy Harmelink" rharmelink
1i
Re: Good news for option quotes users ... possible smfGetYahooPortfo by "Randy Harmelink" rharmelink
1k
Re: Good news for option quotes users ... possible smfGetYahooPortfo by "Randy Harmelink" rharmelink
1m
Re: Good news for option quotes users ... possible smfGetYahooPortfo by "Randy Harmelink" rharmelink
Messages
Thu Aug 11, 2016 9:40 am (PDT) . Posted by:
garyhartling
I logged into Yahoo.com using the Excel Web Query dialog but in trying the sample file smfGetYahooPortfolioView-Example I'm getting some strange output. Here are lines 2-4 of that spreadsheet:
1 2 3 4 7 10 11 12 13 14 15 16 17
Tickers Time & Price Chg & % Chg Volume Chart More Info
MMM 12:15pm EDT $180.87 $2.05 1.15% 626,628 Chart, News, Stats, Options, Board ED 12:15pm EDT 78.2 -0.1 $0.00
Columns 1-7 look all right but then it goes haywire. It looks like columns 13-17 are picking up info from the next ticker "ED" and displaying some of the ED data on the MMM line.
I've included my file, but what you see when you open it up may not be what I see.
1 2 3 4 7 10 11 12 13 14 15 16 17
Tickers Time & Price Chg & % Chg Volume Chart More Info
MMM 12:15pm EDT $180.87 $2.05 1.15% 626,628 Chart, News, Stats, Options, Board ED 12:15pm EDT 78.2 -0.1 $0.00
Columns 1-7 look all right but then it goes haywire. It looks like columns 13-17 are picking up info from the next ticker "ED" and displaying some of the ED data on the MMM line.
I've included my file, but what you see when you open it up may not be what I see.
Attachment(s) from
1 of 1 File(s)
Thu Aug 11, 2016 10:10 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Your attachment looks fine on my machine, because Yahoo is using my "v1"
view definition when the data is retrieved.
In order to get what I see, you would also need to customize your "Basic"
(i.e. "v1") view under Yahoo Portfolios in order to have the same fields
available. Mine are:
TIME & PRICE
CHG & % CHG
DAY'S LOW & HIGH
VOLUME
AVG VOL
MKT CAP
BID
ASK
YIELD
DIV/SHARE
EX-DIV DATE
P/E
PRICE/SALES
1-YR TRGT PRICE
The default "Basic" view only contains:
SYMBOL
TIME & PRICE
CHG & % CHG
DAY'S LOW & HIGH
VOLUME
AVG VOL
MKT CAP
CHART
MORE INFO
So only fields 1 thru 9 would be of any use with that view definition. It
depends on what fields you need. For my purposes, I wanted bid and ask
prices. But they aren't available on any of the default views.
You can always see the "raw" table from your view with something like:
=RCHGetHTMLTable("https://finance.yahoo.com/quotes/MCD,IBM/view/v1
",">Symbol")
On Thu, Aug 11, 2016 at 9:40 AM, Gary.Hartling@ <Gary.Hartling@gmail.com>
...
wrote:
>
> I logged into Yahoo.com using the Excel Web Query dialog but in trying the
> sample file smfGetYahooPortfolioView-Example I'm getting some strange
> output. Here are lines 2-4 of that spreadsheet:
>
> 1 2 3 4 7 10 11 12 13 14 15 16 17
> Tickers Time & Price Chg & % Chg Volume Chart More Info
> MMM 12:15pm EDT $180.87 $2.05 1.15% 626,628 Chart, News, Stats, Options,
> Board ED 12:15pm EDT 78.2 -0.1 $0.00
>
> Columns 1-7 look all right but then it goes haywire. It looks like
> columns 13-17 are picking up info from the next ticker "ED" and displaying
> some of the ED data on the MMM line.
>
> I've included my file, but what you see when you open it up may not be
> what I see.
>
view definition when the data is retrieved.
In order to get what I see, you would also need to customize your "Basic"
(i.e. "v1") view under Yahoo Portfolios in order to have the same fields
available. Mine are:
TIME & PRICE
CHG & % CHG
DAY'S LOW & HIGH
VOLUME
AVG VOL
MKT CAP
BID
ASK
YIELD
DIV/SHARE
EX-DIV DATE
P/E
PRICE/SALES
1-YR TRGT PRICE
The default "Basic" view only contains:
SYMBOL
TIME & PRICE
CHG & % CHG
DAY'S LOW & HIGH
VOLUME
AVG VOL
MKT CAP
CHART
MORE INFO
So only fields 1 thru 9 would be of any use with that view definition. It
depends on what fields you need. For my purposes, I wanted bid and ask
prices. But they aren't available on any of the default views.
You can always see the "raw" table from your view with something like:
=RCHGetHTMLTable("https://finance.yahoo.com/quotes/MCD,IBM/view/v1
",">Symbol")
On Thu, Aug 11, 2016 at 9:40 AM, Gary.Hartling@ <Gary.Hartling@gmail.com>
...
wrote:
>
> I logged into Yahoo.com using the Excel Web Query dialog but in trying the
> sample file smfGetYahooPortfoli
> output. Here are lines 2-4 of that spreadsheet:
>
> 1 2 3 4 7 10 11 12 13 14 15 16 17
> Tickers Time & Price Chg & % Chg Volume Chart More Info
> MMM 12:15pm EDT $180.87 $2.05 1.15% 626,628 Chart, News, Stats, Options,
> Board ED 12:15pm EDT 78.2 -0.1 $0.00
>
> Columns 1-7 look all right but then it goes haywire. It looks like
> columns 13-17 are picking up info from the next ticker "ED" and displaying
> some of the ED data on the MMM line.
>
> I've included my file, but what you see when you open it up may not be
> what I see.
>
Thu Aug 11, 2016 11:45 am (PDT) . Posted by:
garyhartling
Under "My Portfoliois" I went to Add Custom View where I initially created a view called "v1" with all your fields. That didn't work so I renamed that to "Basic" but nothing changed. Going back to Add Custom View it shows 7 views listed on the right side. They are Basic, Details, Pre/After Market, Fundamentals, Moving Averages, Estimates, and Portfolios. The Yahoo Basic view, the first in the last sentence has the following fields:
Symbol Name
Currency Exchange
Time & Price Price
Last Trd Time Chg & % Chg
Change % Chg
Volume Avg Vol
More Info Chart
The "Basic" view I created doesn't seem to affect Yahoo's Basic view. How do you change Yahoo's Basic view?
Symbol Name
Currency Exchange
Time & Price Price
Last Trd Time Chg & % Chg
Change % Chg
Volume Avg Vol
More Info Chart
The "Basic" view I created doesn't seem to affect Yahoo's Basic view. How do you change Yahoo's Basic view?
Thu Aug 11, 2016 11:52 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Why are you renaming the views?
Yahoo's "Basic" view is assigned the "v1" view tag. You can rename it to
anything you want and it will still be the "v1" view. You can even create a
second "Basic" view, but it will have a different view tag in the URL.
When you click on a view tab in the portfolio, look at the URL for it. In
FireFox, I can hover over the tabs, and it will tell me what the URL is for
each one. You want to change the one that has "v1" in the URL.
On Thu, Aug 11, 2016 at 11:45 AM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Under "My Portfoliois" I went to Add Custom View where I initially created
> a view called "v1" with all your fields. That didn't work so I renamed that
> to "Basic" but nothing changed. Going back to Add Custom View it shows 7
> views listed on the right side. They are Basic, Details, Pre/After Market,
> Fundamentals, Moving Averages, Estimates, and Portfolios. The Yahoo Basic
> view, the first in the last sentence has the following fields:
>
> Symbol Name
> Currency Exchange
> Time & Price Price
> Last Trd Time Chg & % Chg
> Change % Chg
> Volume Avg Vol
> More Info Chart
>
> The "Basic" view I created doesn't seem to affect Yahoo's Basic view. How
> do you change Yahoo's Basic view?
>
Yahoo's "Basic" view is assigned the "v1" view tag. You can rename it to
anything you want and it will still be the "v1" view. You can even create a
second "Basic" view, but it will have a different view tag in the URL.
When you click on a view tab in the portfolio, look at the URL for it. In
FireFox, I can hover over the tabs, and it will tell me what the URL is for
each one. You want to change the one that has "v1" in the URL.
On Thu, Aug 11, 2016 at 11:45 AM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Under "My Portfoliois" I went to Add Custom View where I initially created
> a view called "v1" with all your fields. That didn't work so I renamed that
> to "Basic" but nothing changed. Going back to Add Custom View it shows 7
> views listed on the right side. They are Basic, Details, Pre/After Market,
> Fundamentals, Moving Averages, Estimates, and Portfolios. The Yahoo Basic
> view, the first in the last sentence has the following fields:
>
> Symbol Name
> Currency Exchange
> Time & Price Price
> Last Trd Time Chg & % Chg
> Change % Chg
> Volume Avg Vol
> More Info Chart
>
> The "Basic" view I created doesn't seem to affect Yahoo's Basic view. How
> do you change Yahoo's Basic view?
>
Thu Aug 11, 2016 12:32 pm (PDT) . Posted by:
garyhartling
OK, so in the first tab under "My Portfolios" I can see the "v1" in the URL and that view now has all your fields in it. But that doesn't change anything in the example spreadsheet.
Thu Aug 11, 2016 1:35 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
BTW, you did do an smfForceRecalculation after changing your view on Yahoo,
right? It needs to pick up a new copy of the web page, with that definition.
On Thu, Aug 11, 2016 at 12:32 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> OK, so in the first tab under "My Portfolios" I can see the "v1" in the
> URL and that view now has all your fields in it. But that doesn't change
> anything in the example spreadsheet.
>
right? It needs to pick up a new copy of the web page, with that definition.
On Thu, Aug 11, 2016 at 12:32 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> OK, so in the first tab under "My Portfolios" I can see the "v1" in the
> URL and that view now has all your fields in it. But that doesn't change
> anything in the example spreadsheet.
>
Thu Aug 11, 2016 1:39 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
What fields do you see with:
=RCHGetHTMLTable("https://finance.yahoo.com/quotes/MCD,IBM/view/v1
",">Symbol")
On Thu, Aug 11, 2016 at 12:32 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> OK, so in the first tab under "My Portfolios" I can see the "v1" in the
> URL and that view now has all your fields in it. But that doesn't change
> anything in the example spreadsheet.
>
>
=RCHGetHTMLTable("https://finance.yahoo.com/quotes/MCD,IBM/view/v1
",">Symbol")
On Thu, Aug 11, 2016 at 12:32 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> OK, so in the first tab under "My Portfolios" I can see the "v1" in the
> URL and that view now has all your fields in it. But that doesn't change
> anything in the example spreadsheet.
>
>
Thu Aug 11, 2016 3:01 pm (PDT) . Posted by:
garyhartling
TIME & PRICE CHG & % CHG DAY'S LOW & HIGH VOLUME AVG VOL MKT CAP BID ASK YIELD DIV/SHARE EX-DIV DATE P/E PRICE/SALES 1-YR TRGT PRICE
with two lines that look like the right info for MCD and IBM, but without those tickers showing.
with two lines that look like the right info for MCD and IBM, but without those tickers showing.
Thu Aug 11, 2016 4:02 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Looks like you have everything set up correctly. And the template isn't
working? Did you try smfForceRecalculation?
BTW, when I've been trying these things in the browser, I sometimes get an
error message from Yahoo that says to try again later. If that happens with
the function, all spaces will be returned. It's usually fixed by hitting
smfForceRecalculation again. Another way would be to go down and add a
symbol you don't care about. I sometimes do an "x" or a "y".
If you want the function to return symbols, just use the "00" or 0 field
number.
On Thu, Aug 11, 2016 at 3:01 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> TIME & PRICECHG & % CHGDAY'S LOW & HIGHVOLUMEAVG VOLMKT CAPBIDASKYIELD
> DIV/SHAREEX-DIV DATEP/EPRICE/SALES1-YR TRGT PRICE
>
>
> with two lines that look like the right info for MCD and IBM, but without
> those tickers showing.
>
>
working? Did you try smfForceRecalculation?
BTW, when I've been trying these things in the browser, I sometimes get an
error message from Yahoo that says to try again later. If that happens with
the function, all spaces will be returned. It's usually fixed by hitting
smfForceRecalculation again. Another way would be to go down and add a
symbol you don't care about. I sometimes do an "x" or a "y".
If you want the function to return symbols, just use the "00" or 0 field
number.
On Thu, Aug 11, 2016 at 3:01 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> TIME & PRICECHG & % CHGDAY'S LOW & HIGHVOLUMEAVG VOLMKT CAPBIDASKYIELD
> DIV/SHAREEX-
>
>
> with two lines that look like the right info for MCD and IBM, but without
> those tickers showing.
>
>
Thu Aug 11, 2016 4:42 pm (PDT) . Posted by:
garyhartling
I've exited Excel and started the sample spreadsheet up again and I just added a ReCalc button, but nothing changes the spreadsheet. I haven't gotten the message to try again later.
Thu Aug 11, 2016 5:12 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Try these two array-entered formulas:
=RCHGetHTMLTable("https://finance.yahoo.com/quotes/MCD,IBM/view/v1
",">Symbol")
=smfGetYahooPortfolioView("MCD,IBM",,,1)
They should be very similar, but there are three differences you should be
able to notice:
1. The ticker symbols are cleaned up on the second formula.
2. The yield has been adjusted to be a value that can be formatted as a
percentage
3. The ex-div date has been converted from a text string to a serial date
value
Such as:
Symbol Time & Price Chg & % Chg Day's Low & High Volume Avg Vol Mkt
Cap Bid Ask Yield Div/Share Ex-Div Date P/E Price/Sales 1-Yr Trgt Price
MCD 04:00pm EDT 119.38 0.58 0.0049 118.85 119.75 3489115 5424580 101870000
119.1 119.49 3.01 3.56 Tue, Aug 30, 2016, 1:00AM EDT 22.8 4.03 130.29
IBM 04:01pm EDT 163.53 1.45 0.0089 162.18 164.95 8768585 3368000 156310000
163.23 163.53 3.46 5.6 Mon, Aug 8, 2016, 1:00AM EDT 13.3 1.93 153.9
Symbol Time & Price Chg & % Chg Day's Low & High Volume Avg Vol Mkt
Cap Bid Ask Yield Div/Share Ex-Div Date P/E Price/Sales 1-Yr Trgt Price
MCD 04:00pm EDT 119.38 0.58 0.0049 118.85 119.75 3489115 5424580 101870000
119.1 119.49 0.0301 3.56 42612 22.8 4.03 130.29
IBM 04:01pm EDT 163.53 1.45 0.0089 162.18 164.95 8768585 3368000 156310000
163.23 163.53 0.0346 5.6 42590 13.3 1.93 153.9
On Thu, Aug 11, 2016 at 4:42 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> I've exited Excel and started the sample spreadsheet up again and I just
> added a ReCalc button, but nothing changes the spreadsheet. I haven't
> gotten the message to try again later.
>
>
>
=RCHGetHTMLTable("https://finance.yahoo.com/quotes/MCD,IBM/view/v1
",">Symbol")
=smfGetYahooPortfolioView("MCD,IBM",,,1)
They should be very similar, but there are three differences you should be
able to notice:
1. The ticker symbols are cleaned up on the second formula.
2. The yield has been adjusted to be a value that can be formatted as a
percentage
3. The ex-div date has been converted from a text string to a serial date
value
Such as:
Symbol Time & Price Chg & % Chg Day's Low & High Volume Avg Vol Mkt
Cap Bid Ask Yield Div/Share Ex-Div Date P/E Price/Sales 1-Yr Trgt Price
MCD 04:00pm EDT 119.38 0.58 0.0049 118.85 119.75 3489115 5424580 101870000
119.1 119.49 3.01 3.56 Tue, Aug 30, 2016, 1:00AM EDT 22.8 4.03 130.29
IBM 04:01pm EDT 163.53 1.45 0.0089 162.18 164.95 8768585 3368000 156310000
163.23 163.53 3.46 5.6 Mon, Aug 8, 2016, 1:00AM EDT 13.3 1.93 153.9
Symbol Time & Price Chg & % Chg Day's Low & High Volume Avg Vol Mkt
Cap Bid Ask Yield Div/Share Ex-Div Date P/E Price/Sales 1-Yr Trgt Price
MCD 04:00pm EDT 119.38 0.58 0.0049 118.85 119.75 3489115 5424580 101870000
119.1 119.49 0.0301 3.56 42612 22.8 4.03 130.29
IBM 04:01pm EDT 163.53 1.45 0.0089 162.18 164.95 8768585 3368000 156310000
163.23 163.53 0.0346 5.6 42590 13.3 1.93 153.9
On Thu, Aug 11, 2016 at 4:42 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> I've exited Excel and started the sample spreadsheet up again and I just
> added a ReCalc button, but nothing changes the spreadsheet. I haven't
> gotten the message to try again later.
>
>
>
Thu Aug 11, 2016 5:57 pm (PDT) . Posted by:
garyhartling
Hope you can see this.
Attachment(s) from
1 of 1 Photo(s)
Thu Aug 11, 2016 6:38 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Yup. They were both showing the Yahoo default view for "v1". So if you've
made changes to it, you probably don't have the security cookie in place
for EXCEL and the add-in to use.
You can double check by using the EXCEL Web Query dialog and navigate to
https://finance.yahoo.com/quotes/MCD,IBM/view/v1
...and see if you get the default view or your customized view. You can
just cancel out. You don't need to import anything.
Or, in the EXCEL Web Query dialog, navigate to:
https://login.yahoo.com/config/login
...and see if it says to "Sign in" (meaning you need to log in) or asks if
it's "Not you?" (meaning you are logged in).
On Thu, Aug 11, 2016 at 5:57 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Hope you can see this.
>
made changes to it, you probably don't have the security cookie in place
for EXCEL and the add-in to use.
You can double check by using the EXCEL Web Query dialog and navigate to
https://finance.yahoo.com/quotes/MCD,IBM/view/v1
...and see if you get the default view or your customized view. You can
just cancel out. You don't need to import anything.
Or, in the EXCEL Web Query dialog, navigate to:
https://login.yahoo.com/config/login
...and see if it says to "Sign in" (meaning you need to log in) or asks if
it's "Not you?" (meaning you are logged in).
On Thu, Aug 11, 2016 at 5:57 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Hope you can see this.
>
Thu Aug 11, 2016 8:45 pm (PDT) . Posted by:
garyhartling
OK, so I did this:
You can double check by using the EXCEL Web Query dialog and navigate to
https://finance.yahoo.com/quotes/MCD,IBM/view/v1 https://finance.yahoo.com/quotes/MCD,IBM/view/v1
and I got the default view, not my cusomized view. So I did this:
Or, in the EXCEL Web Query dialog, navigate to:
https://login.yahoo.com/config/login https://login.yahoo.com/config/login
and logged in, and then went to
https://finance.yahoo.com/quotes/MCD,IBM/view/v1 https://finance.yahoo.com/quotes/MCD,IBM/view/v1
and I see the customized view.
Then I go to the example spreadsheet, Recalculate, and
I see the picture attached. The field names are there, but a bit jumbled up, but only 1 line
of data and mostly wrong. I seem to be going backwards.
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
Yup. They were both showing the Yahoo default view for "v1". So if you've made changes to it, you probably don't have the security cookie in place for EXCEL and the add-in to use.
You can double check by using the EXCEL Web Query dialog and navigate to
https://finance.yahoo.com/quotes/MCD,IBM/view/v1 https://finance.yahoo.com/quotes/MCD,IBM/view/v1
...and see if you get the default view or your customized view. You can just cancel out. You don't need to import anything.
Or, in the EXCEL Web Query dialog, navigate to:
https://login.yahoo.com/config/login https://login.yahoo.com/config/login
...and see if it says to "Sign in" (meaning you need to log in) or asks if it's "Not you?" (meaning you are logged in).
On Thu, Aug 11, 2016 at 5:57 PM, Gary.Hartling@... mailto:Gary.Hartling@... [smf_addin] <smf_addin@yahoogroups.com mailto:smf_addin@yahoogroups.com> wrote:
Hope you can see this.
You can double check by using the EXCEL Web Query dialog and navigate to
https://finance.yahoo.com/quotes/MCD,IBM/view/v1 https://finance.yahoo.com/quotes/MCD,IBM/view/v1
and I got the default view, not my cusomized view. So I did this:
Or, in the EXCEL Web Query dialog, navigate to:
https://login.yahoo.com/config/login https://login.yahoo.com/config/login
and logged in, and then went to
https://finance.yahoo.com/quotes/MCD,IBM/view/v1 https://finance.yahoo.com/quotes/MCD,IBM/view/v1
and I see the customized view.
Then I go to the example spreadsheet, Recalculate, and
I see the picture attached. The field names are there, but a bit jumbled up, but only 1 line
of data and mostly wrong. I seem to be going backwards.
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
Yup. They were both showing the Yahoo default view for "v1". So if you've made changes to it, you probably don't have the security cookie in place for EXCEL and the add-in to use.
You can double check by using the EXCEL Web Query dialog and navigate to
https://finance.yahoo.com/quotes/MCD,IBM/view/v1 https://finance.yahoo.com/quotes/MCD,IBM/view/v1
...and see if you get the default view or your customized view. You can just cancel out. You don't need to import anything.
Or, in the EXCEL Web Query dialog, navigate to:
https://login.yahoo.com/config/login https://login.yahoo.com/config/login
...and see if it says to "Sign in" (meaning you need to log in) or asks if it's "Not you?" (meaning you are logged in).
On Thu, Aug 11, 2016 at 5:57 PM, Gary.Hartling@... mailto:Gary.Hartling@... [smf_addin] <smf_addin@yahoogroups.com mailto:smf_addin@yahoogroups.com> wrote:
Hope you can see this.
Attachment(s) from
1 of 1 Photo(s)
Thu Aug 11, 2016 6:21 pm (PDT) . Posted by:
t_mcclenahan
Thank you. This worked for me as well.
~Tim
~Tim
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar