15 Messages
Digest #4313
Messages
Mon Mar 12, 2018 3:15 pm (PDT) . Posted by:
eclipsme
Lately, for perhaps a week or more, I am getting 2 dashes, "--" in some of the results. I figure this must be a Yahoo issue, but it is not going away. Maybe they have changed something?
I am using this formula:
=smfGetYahooPortfolioView($B$4:$B$411,$C$2:$AJ$2,,1)
B has tickers and Row 2 has the various data fields.
Fields 80, ex-dividend date, 36, Forward Dividend Yield, and field 34, Dividend Yield, are not get populated for some tickers, instead the "--" shows up.
Tickers such as; ADP,CAH,CSCO, CVS, and DPS are showing this behavior.
IBM, INTC, ITW, JNJ, ITW, JNJ, KMB, and KO do not.
Any ideas?
Thanks for looking!
I am using this formula:
=smfGetYahooPortfol
B has tickers and Row 2 has the various data fields.
Fields 80, ex-dividend date, 36, Forward Dividend Yield, and field 34, Dividend Yield, are not get populated for some tickers, instead the "--" shows up.
Tickers such as; ADP,CAH,CSCO, CVS, and DPS are showing this behavior.
IBM, INTC, ITW, JNJ, ITW, JNJ, KMB, and KO do not.
Any ideas?
Thanks for looking!
Mon Mar 12, 2018 4:11 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Looks like a (hopefully temporary) Yahoo problem. Various data items are
missing in the JSON file and on their Key Statistics pages...and it seems
to change over different refreshes/updates?
On Mon, Mar 12, 2018 at 1:36 PM, harvey@
...
wrote:
> Lately, for perhaps a week or more, I am getting 2 dashes, "--" in some of
> the results. I figure this must be a Yahoo issue, but it is not going away.
> Maybe they have changed something?
>
> I am using this formula:
> =smfGetYahooPortfolioView($B$4:$B$411,$C$2:$AJ$2,,1)
>
> B has tickers and Row 2 has the various data fields.
>
> Fields 80, ex-dividend date, 36, Forward Dividend Yield, and field 34,
> Dividend Yield, are not get populated for some tickers, instead the "--"
> shows up.
>
> Tickers such as; ADP,CAH,CSCO, CVS, and DPS are showing this behavior.
> IBM, INTC, ITW, JNJ, ITW, JNJ, KMB, and KO do not.
>
> Any ideas?
> Thanks for looking!
>
missing in the JSON file and on their Key Statistics pages...and it seems
to change over different refreshes/updates?
On Mon, Mar 12, 2018 at 1:36 PM, harvey@
...
wrote:
> Lately, for perhaps a week or more, I am getting 2 dashes, "--" in some of
> the results. I figure this must be a Yahoo issue, but it is not going away.
> Maybe they have changed something?
>
> I am using this formula:
> =smfGetYahooPortfol
>
> B has tickers and Row 2 has the various data fields.
>
> Fields 80, ex-dividend date, 36, Forward Dividend Yield, and field 34,
> Dividend Yield, are not get populated for some tickers, instead the "--"
> shows up.
>
> Tickers such as; ADP,CAH,CSCO, CVS, and DPS are showing this behavior.
> IBM, INTC, ITW, JNJ, ITW, JNJ, KMB, and KO do not.
>
> Any ideas?
> Thanks for looking!
>
Mon Mar 12, 2018 4:22 pm (PDT) . Posted by:
johnross999
Hi Randy,
Can you please post the documentation for the new smfGetOptionExpirations function. I am not sure where to add the type (weekly, monthly, ...).
TX
Can you please post the documentation for the new smfGetOptionExpirat
TX
Mon Mar 12, 2018 6:18 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sorry. Now updated.
On Mon, Mar 12, 2018 at 4:22 PM, johnrr9@
...
wrote:
>
> Can you please post the documentation for the new smfGetOptionExpirations
> function. I am not sure where to add the type (weekly, monthly, ...).
>
On Mon, Mar 12, 2018 at 4:22 PM, johnrr9@
...
wrote:
>
> Can you please post the documentation for the new smfGetOptionExpirat
> function. I am not sure where to add the type (weekly, monthly, ...).
>
Wed Mar 14, 2018 7:07 am (PDT) . Posted by:
"Higrm" higrm
Hi Group,I'm trying to extract a single value from the table at this URL: http://xe.com/currencytables/?from=USD&date=2017-10-16For the particular dates I need to due my tax filing, (need to convert dividends collected in $ to Euro with the exchange rate in effect on that day.)
I have used the Quick Web Page Examination workbook, but it gives me different numbers than what appear on the web site if I change the URL on the workbook, versus if I change the date on the web site. For example, I want the USD/EUR figure on Feb. 16, 2017. When I go to the web site, I get a value of 1.0660072850 USD to EURO. With table Extract tab, using the same URL above and with Begin Direction 1 End direction 1, I get 0.92755 USD to EURO? If I change the URL to a different date I get other numbers and they don't match either.
I then tried the Table Cell tab and thought I had figured it out. I managed to get the correct results with this function call:=RCHGetTableCell("http://xe.com/currencytables/?from=USD&date="&TEXT(C34,"YYYY-MM-DD"),4,,,,,3,"</table",,"--")Where C34 contains the date of the transaction stored as a date. This worked for most of the dates, but on some dates, it gives completely wrong values.. For instance when I have the date Nov. 22, 2017 it brings back 0.370748322. I went back to the Quick Web Page Examination workbook and tried this URL:http://xe.com/currencytables/?from=USD&date=2017-11-22 Now instead of a single USD line, I get two lines with the second one containing rubbish. So when I have the wrong hard coded to bring back the 3rd item, it is giving me the rubbish second USD to USD value.
I experimented and changed the row parameter from 3 to 4 in my table in the call =RCHGetTableCell("http://xe.com/currencytables/?from=USD&date="&TEXT(C34,"YYYY-MM-DD"),4,,,,,3,"</table",,"--") and I got the correct number. Then I tried it in other cells with the obvious wrong value and it worked there as well. But then when I went to another cell, those cells that I had just changed, once again had a different wrong number. I went back and changed the row parameter back to 3 from 4 and then the correct number from the web site was there again. Now I have the full year of dates for all twelve months with the correct conversion factor and all the formula contain 3 as the row parameter except the first one! It shows the correct value with a 4 as the row parameter. Well I quickly copied the values down and will let you either confirm this strange behaviour or write me off as a lunatic.
Thanks,Higrm
PS, the filters don't seem to have any effect on what is brought back from this particular web site. If I enter EUR as the first filter, I still get the full table from the top row.
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Have you tried the highest rated email app? With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage. For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF Visit Your Group
- New Members 5
• Privacy • Unsubscribe • Terms of Use
.
I have used the Quick Web Page Examination workbook, but it gives me different numbers than what appear on the web site if I change the URL on the workbook, versus if I change the date on the web site. For example, I want the USD/EUR figure on Feb. 16, 2017. When I go to the web site, I get a value of 1.0660072850 USD to EURO. With table Extract tab, using the same URL above and with Begin Direction 1 End direction 1, I get 0.92755 USD to EURO? If I change the URL to a different date I get other numbers and they don't match either.
I then tried the Table Cell tab and thought I had figured it out. I managed to get the correct results with this function call:=RCHGetTableCell("http://xe.com/currencytables/?from=USD&date="&TEXT(C34,"YYYY-MM-DD"),4,,,,,3,"</table",,"--")Where C34 contains the date of the transaction stored as a date. This worked for most of the dates, but on some dates, it gives completely wrong values.. For instance when I have the date Nov. 22, 2017 it brings back 0.370748322. I went back to the Quick Web Page Examination workbook and tried this URL:http://xe.com/currencytables/?from=USD&date=2017-11-22 Now instead of a single USD line, I get two lines with the second one containing rubbish. So when I have the wrong hard coded to bring back the 3rd item, it is giving me the rubbish second USD to USD value.
I experimented and changed the row parameter from 3 to 4 in my table in the call =RCHGetTableCell("http://xe.com/currencytables/?from=USD&date="&TEXT(C34,"YYYY-MM-DD"),4,,,,,3,"</table",,"--") and I got the correct number. Then I tried it in other cells with the obvious wrong value and it worked there as well. But then when I went to another cell, those cells that I had just changed, once again had a different wrong number. I went back and changed the row parameter back to 3 from 4 and then the correct number from the web site was there again. Now I have the full year of dates for all twelve months with the correct conversion factor and all the formula contain 3 as the row parameter except the first one! It shows the correct value with a 4 as the row parameter. Well I quickly copied the values down and will let you either confirm this strange behaviour or write me off as a lunatic.
Thanks,Higrm
PS, the filters don't seem to have any effect on what is brought back from this particular web site. If I enter EUR as the first filter, I still get the full table from the top row.
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Have you tried the highest rated email app? With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage. For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF Visit Your Group
- New Members 5
• Privacy • Unsubscribe • Terms of Use
.
Wed Mar 14, 2018 10:23 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Unless EUR is always the same location in the table, using the 4 and 3
would not be the right way to do the extract. Try something like:
=RCHGetTableCell("http://xe.com/currencytables/?from=USD&date="&TEXT(C36,"YYYY-MM-DD"),3,"Units
per USD",">EUR")
That seems to work consistently for me on the dates you mention?
On Wed, Mar 14, 2018 at 6:57 AM, Higrm higrm@
...
wrote:
>
> I'm trying to extract a single value from the table at this URL:
> http://xe.com/currencytables/?from=USD&date=2017-10-16
> For the particular dates I need to due my tax filing, (need to convert
> dividends collected in $ to Euro with the exchange rate in effect on that
> day.)
>
> I have used the Quick Web Page Examination workbook, but it gives me
> different numbers than what appear on the web site if I change the URL on
> the workbook, versus if I change the date on the web site. For example, I
> want the USD/EUR figure on Feb. 16, 2017. When I go to the web site, I get
> a value of 1.0660072850 USD to EURO. With table Extract tab, using the
> same URL above and with Begin Direction 1 End direction 1, I get 0.92755
> USD to EURO? If I change the URL to a different date I get other numbers
> and they don't match either.
>
> I then tried the Table Cell tab and thought I had figured it out. I
> managed to get the correct results with this function call:
> =RCHGetTableCell("http://xe.com/currencytables/?from=USD&date=
> "&TEXT(C34,"YYYY-MM-DD"),4,,,,,3,"</table",,"--")
> Where C34 contains the date of the transaction stored as a date. This
> worked for most of the dates, but on some dates, it gives completely wrong
> values. For instance when I have the date Nov. 22, 2017 it brings back
> 0.370748322. I went back to the Quick Web Page Examination workbook and
> tried this URL:http://xe.com/currencytables/?from=USD&date=2017-11-22
> Now instead of a single USD line, I get two lines with the second one
> containing rubbish. So when I have the wrong hard coded to bring back the
> 3rd item, it is giving me the rubbish second USD to USD value.
>
> I experimented and changed the row parameter from 3 to 4 in my table in
> the call =RCHGetTableCell("http://xe.com/currencytables/?from=USD&date=
> "&TEXT(C34,"YYYY-MM-DD"),4,,,,,*3*,"</table",,"--") and I got the correct
> number. Then I tried it in other cells with the obvious wrong value and it
> worked there as well. But then when I went to another cell, those cells
> that I had just changed, once again had a different wrong number. I went
> back and changed the row parameter back to 3 from 4 and then the correct
> number from the web site was there again. Now I have the full year of
> dates for all twelve months with the correct conversion factor and all the
> formula contain 3 as the row parameter except the first one! It shows the
> correct value with a 4 as the row parameter. Well I quickly copied the
> values down and will let you either confirm this strange behaviour or write
> me off as a lunatic.
>
> Thanks,
> Higrm
>
> PS, the filters don't seem to have any effect on what is brought back from
> this particular web site. If I enter EUR as the first filter, I still get
> the full table from the top row.
>
>
would not be the right way to do the extract. Try something like:
=RCHGetTableCell("http://xe.com/currencytables/?from=USD&date="&TEXT(C36,"YYYY-MM-DD"),3,"Units
per USD",">EUR")
That seems to work consistently for me on the dates you mention?
On Wed, Mar 14, 2018 at 6:57 AM, Higrm higrm@
...
wrote:
>
> I'm trying to extract a single value from the table at this URL:
> http://xe.com/currencytables/?from=USD&date=2017-10-16
> For the particular dates I need to due my tax filing, (need to convert
> dividends collected in $ to Euro with the exchange rate in effect on that
> day.)
>
> I have used the Quick Web Page Examination workbook, but it gives me
> different numbers than what appear on the web site if I change the URL on
> the workbook, versus if I change the date on the web site. For example, I
> want the USD/EUR figure on Feb. 16, 2017. When I go to the web site, I get
> a value of 1.0660072850 USD to EURO. With table Extract tab, using the
> same URL above and with Begin Direction 1 End direction 1, I get 0.92755
> USD to EURO? If I change the URL to a different date I get other numbers
> and they don't match either.
>
> I then tried the Table Cell tab and thought I had figured it out. I
> managed to get the correct results with this function call:
> =RCHGetTableCell("http://xe.com/currencytables/?from=USD&date=
> "&TEXT(C34,"YYYY-MM-DD"),4,,,,,3,"</table",,"--")
> Where C34 contains the date of the transaction stored as a date. This
> worked for most of the dates, but on some dates, it gives completely wrong
> values. For instance when I have the date Nov. 22, 2017 it brings back
> 0.370748322. I went back to the Quick Web Page Examination workbook and
> tried this URL:http://xe.com/currencytables/?from=USD&date=2017-11-22
> Now instead of a single USD line, I get two lines with the second one
> containing rubbish. So when I have the wrong hard coded to bring back the
> 3rd item, it is giving me the rubbish second USD to USD value.
>
> I experimented and changed the row parameter from 3 to 4 in my table in
> the call =RCHGetTableCell("http://xe.com/currencytables/?from=USD&date=
> "&TEXT(
> number. Then I tried it in other cells with the obvious wrong value and it
> worked there as well. But then when I went to another cell, those cells
> that I had just changed, once again had a different wrong number. I went
> back and changed the row parameter back to 3 from 4 and then the correct
> number from the web site was there again. Now I have the full year of
> dates for all twelve months with the correct conversion factor and all the
> formula contain 3 as the row parameter except the first one! It shows the
> correct value with a 4 as the row parameter. Well I quickly copied the
> values down and will let you either confirm this strange behaviour or write
> me off as a lunatic.
>
> Thanks,
> Higrm
>
> PS, the filters don't seem to have any effect on what is brought back from
> this particular web site. If I enter EUR as the first filter, I still get
> the full table from the top row.
>
>
Wed Mar 14, 2018 4:47 pm (PDT) . Posted by:
"Higrm" higrm
Thanks a bunch Randy, that does seem to solve the problem.
Do you have an idea why the function returns inconsistent data as I originally wrote it? Could this be linked to the web page cache? I'm not sure if changing the date is changing the web page as perceived by the function, so maybe it is merging data is some way. Just a guess, but if I forced a recalculation while I had several rows of retrieved data, the values retrieved would change.
On the web page, the position is always consistent regardless of the date. And why do we need to use ">EUR", instead of just "EUR"? What is the ">" doing?
Thanks again for your super speedy replies and this great functionality.Higrm
On Wednesday, March 14, 2018, 6:24:38 PM GMT+1, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Unless EUR is always the same location in the table, using the 4 and 3 would not be the right way to do the extract. Try something like:
=RCHGetTableCell("http://xe.com/currencytables/?from=USD&date="&TEXT(C36,"YYYY-MM-DD"),3,"Units per USD",">EUR")
That seems to work consistently for me on the dates you mention?
On Wed, Mar 14, 2018 at 6:57 AM, Higrm higrm@... wrote:
I'm trying to extract a single value from the table at this URL: http://xe.com/currencytables/? from=USD&date=2017-10-16For the particular dates I need to due my tax filing, (need to convert dividends collected in $ to Euro with the exchange rate in effect on that day.)
I have used the Quick Web Page Examination workbook, but it gives me different numbers than what appear on the web site if I change the URL on the workbook, versus if I change the date on the web site. For example, I want the USD/EUR figure on Feb. 16, 2017. When I go to the web site, I get a value of 1.0660072850 USD to EURO. With table Extract tab, using the same URL above and with Begin Direction 1 End direction 1, I get 0.92755 USD to EURO? If I change the URL to a different date I get other numbers and they don't match either.
I then tried the Table Cell tab and thought I had figured it out. I managed to get the correct results with this function call:=RCHGetTableCell("http://xe. com/currencytables/?from=USD& date="&TEXT(C34,"YYYY-MM-DD"), 4,,,,,3,"</table",,"--")Where C34 contains the date of the transaction stored as a date. This worked for most of the dates, but on some dates, it gives completely wrong values. For instance when I have the date Nov. 22, 2017 it brings back 0..370748322. I went back to the Quick Web Page Examination workbook and tried this URL:http://xe.com/ currencytables/?from=USD&date= 2017-11-22 Now instead of a single USD line, I get two lines with the second one containing rubbish. So when I have the wrong hard coded to bring back the 3rd item, it is giving me the rubbish second USD to USD value.
I experimented and changed the row parameter from 3 to 4 in my table in the call =RCHGetTableCell("http://xe. com/currencytables/?from=USD& date="&TEXT(C34,"YYYY-MM-DD"), 4,,,,,3,"</table",,"--") and I got the correct number. Then I tried it in other cells with the obvious wrong value and it worked there as well. But then when I went to another cell, those cells that I had just changed, once again had a different wrong number. I went back and changed the row parameter back to 3 from 4 and then the correct number from the web site was there again. Now I have the full year of dates for all twelve months with the correct conversion factor and all the formula contain 3 as the row parameter except the first one! It shows the correct value with a 4 as the row parameter. Well I quickly copied the values down and will let you either confirm this strange behaviour or write me off as a lunatic.
Thanks,Higrm
PS, the filters don't seem to have any effect on what is brought back from this particular web site. If I enter EUR as the first filter, I still get the full table from the top row.
Do you have an idea why the function returns inconsistent data as I originally wrote it? Could this be linked to the web page cache? I'm not sure if changing the date is changing the web page as perceived by the function, so maybe it is merging data is some way. Just a guess, but if I forced a recalculation while I had several rows of retrieved data, the values retrieved would change.
On the web page, the position is always consistent regardless of the date. And why do we need to use ">EUR", instead of just "EUR"? What is the ">" doing?
Thanks again for your super speedy replies and this great functionality.Higrm
On Wednesday, March 14, 2018, 6:24:38 PM GMT+1, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Unless EUR is always the same location in the table, using the 4 and 3 would not be the right way to do the extract. Try something like:
=RCHGetTableCell("http://xe.com/currencytables/?from=USD&date="&TEXT(C36,"YYYY-MM-DD"),3,"Units per USD",">EUR")
That seems to work consistently for me on the dates you mention?
On Wed, Mar 14, 2018 at 6:57 AM, Higrm higrm@... wrote:
I'm trying to extract a single value from the table at this URL: http://xe.com/currencytables/? from=USD&date=2017-10-16For the particular dates I need to due my tax filing, (need to convert dividends collected in $ to Euro with the exchange rate in effect on that day.)
I have used the Quick Web Page Examination workbook, but it gives me different numbers than what appear on the web site if I change the URL on the workbook, versus if I change the date on the web site. For example, I want the USD/EUR figure on Feb. 16, 2017. When I go to the web site, I get a value of 1.0660072850 USD to EURO. With table Extract tab, using the same URL above and with Begin Direction 1 End direction 1, I get 0.92755 USD to EURO? If I change the URL to a different date I get other numbers and they don't match either.
I then tried the Table Cell tab and thought I had figured it out. I managed to get the correct results with this function call:=RCHGetTableCell("http://xe. com/currencytables/?from=USD& date="&TEXT(C34,"YYYY-MM-DD"), 4,,,,,3,"</table",,"--")Where C34 contains the date of the transaction stored as a date. This worked for most of the dates, but on some dates, it gives completely wrong values. For instance when I have the date Nov. 22, 2017 it brings back 0..370748322. I went back to the Quick Web Page Examination workbook and tried this URL:http://xe.com/ currencytables/?from=USD&date= 2017-11-22 Now instead of a single USD line, I get two lines with the second one containing rubbish. So when I have the wrong hard coded to bring back the 3rd item, it is giving me the rubbish second USD to USD value.
I experimented and changed the row parameter from 3 to 4 in my table in the call =RCHGetTableCell("http://xe. com/currencytables/
Thanks,Higrm
PS, the filters don't seem to have any effect on what is brought back from this particular web site. If I enter EUR as the first filter, I still get the full table from the top row.
Wed Mar 14, 2018 5:13 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The only reason I would expect differences is because the table would be
rendered differently. But I also never saw that when using the browser.
The ">" prefix is a technique I often use to make a search string more
unique. But it only works on web pages that code their HTML that way (it is
the ending ">" of the previous HTML tag, within the source code of the web
page).
On Wed, Mar 14, 2018 at 4:47 PM, Higrm higrm@
...
wrote:
>
> Do you have an idea why the function returns inconsistent data as I
> originally wrote it? Could this be linked to the web page cache? I'm not
> sure if changing the date is changing the web page as perceived by the
> function, so maybe it is merging data is some way. Just a guess, but if I
> forced a recalculation while I had several rows of retrieved data, the
> values retrieved would change.
>
> On the web page, the position is always consistent regardless of the
> date. And why do we need to use ">EUR", instead of just "EUR"? What is
> the ">" doing?
>
> Thanks again for your super speedy replies and this great functionality.
>
>
rendered differently. But I also never saw that when using the browser.
The ">" prefix is a technique I often use to make a search string more
unique. But it only works on web pages that code their HTML that way (it is
the ending ">" of the previous HTML tag, within the source code of the web
page).
On Wed, Mar 14, 2018 at 4:47 PM, Higrm higrm@
...
wrote:
>
> Do you have an idea why the function returns inconsistent data as I
> originally wrote it? Could this be linked to the web page cache? I'm not
> sure if changing the date is changing the web page as perceived by the
> function, so maybe it is merging data is some way. Just a guess, but if I
> forced a recalculation while I had several rows of retrieved data, the
> values retrieved would change.
>
> On the web page, the position is always consistent regardless of the
> date. And why do we need to use ">EUR"
> the ">" doing?
>
> Thanks again for your super speedy replies and this great functionality.
>
>
Tue Mar 13, 2018 1:23 am (PDT) . Posted by:
thomas777crown
Yeah Randy,
I would like to extract data from this page, I have tried a few methods but nothing comes up.
https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313 https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313
the table at the bottom the Date,Open,High,Low,Close,Volume,Market Cap
How about I go about doing that?
Cheers
Thomas
I would like to extract data from this page, I have tried a few methods but nothing comes up.
https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313 https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313
the table at the bottom the Date,Open,High,
How about I go about doing that?
Cheers
Thomas
Tue Mar 13, 2018 1:27 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
This works for me:
=RCHGetHTMLTable("
https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313
",">High")
On Tue, Mar 13, 2018 at 1:23 AM, thomas777crown@
...
wrote:
> I would like to extract data from this page, I have tried a few methods
> but nothing comes up.
>
> https://coinmarketcap.com/currencies/bitcoin/historical-
> data/?start=20130428&end=20180313
>
> the table at the bottom the Date,Open,High,Low,Close,Volume,Market Cap
>
> How about I go about doing that?
>
>
>
=RCHGetHTMLTable("
https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313
",">High")
On Tue, Mar 13, 2018 at 1:23 AM, thomas777crown@
...
wrote:
> I would like to extract data from this page, I have tried a few methods
> but nothing comes up.
>
> https://coinmarketcap.com/currencies/bitcoin/historical-
> data/?start=
>
> the table at the bottom the Date,Open,High,
>
> How about I go about doing that?
>
>
>
Tue Mar 13, 2018 2:09 am (PDT) . Posted by:
thomas777crown
Thanks, I tried something similar to that and tired the one you provided its keep on returning the date column.
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
This works for me:
=RCHGetHTMLTable("https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313 https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313",">High")
On Tue, Mar 13, 2018 at 1:23 AM, thomas777crown@ ...
wrote:
I would like to extract data from this page, I have tried a few methods but nothing comes up.
https://coinmarketcap.com/ currencies/bitcoin/historical- data/?start=20130428&end= 20180313 https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313
the table at the bottom the Date,Open,High,
How about I go about doing that?
Tue Mar 13, 2018 2:33 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Are you saying it's not returning the table? Just the first column? You
need to array-enter the function over a range.
On Tue, Mar 13, 2018 at 2:09 AM, thomas777crown@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Thanks, I tried something similar to that and tired the one you provided
> its keep on returning the date column.
>
> ---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
>
> This works for me:
>
> =RCHGetHTMLTable("https://coinmarketcap.com/currencies/
> bitcoin/historical-data/?start=20130428&end=20180313",">High")
>
> On Tue, Mar 13, 2018 at 1:23 AM, thomas777crown@
> ...
> wrote:
>
> I would like to extract data from this page, I have tried a few methods
> but nothing comes up.
>
> https://coinmarketcap.com/ currencies/bitcoin/historical-
> data/?start=20130428&end= 20180313
> <https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313>
>
> the table at the bottom the Date,Open,High,Low,Close, Volume,Market Cap
>
> How about I go about doing that?
>
>
>
need to array-enter the function over a range.
On Tue, Mar 13, 2018 at 2:09 AM, thomas777crown@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Thanks, I tried something similar to that and tired the one you provided
> its keep on returning the date column.
>
> ---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
>
> This works for me:
>
> =RCHGetHTMLTable("https://coinmarketcap.com/currencies/
> bitcoin/historical-data/?start=20130428&end=20180313",">High")
>
> On Tue, Mar 13, 2018 at 1:23 AM, thomas777crown@
> ...
> wrote:
>
> I would like to extract data from this page, I have tried a few methods
> but nothing comes up.
>
> https://coinmarketcap.com/ currencies/bitcoin/historical-
> data/?start=20130428&end= 20180313
> <https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180313>
>
> the table at the bottom the Date,Open,High,
>
> How about I go about doing that?
>
>
>
Wed Mar 14, 2018 12:14 pm (PDT) . Posted by:
jumonville
This VBA code...
Public Function fncStockPrice(Ticker As String, item As String) As Double
Dim strURL As String, strCSV As Double, itemFound As Integer, tag As String
itemFound = 0
If item = "latestPrice" Then
tag = "latestPrice"
itemFound = 1
End If
If itemFound = 1 Then
strURL = "https://api.iextrading.com/1.0/stock/" & Ticker & "/quote/" & tag
Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
XMLHTTP.Open "GET", strURL, False
XMLHTTP.send
If XMLHTTP.responseText = "Unknown symbol" Then
fncStockPrice = 0
Else
fncStockPrice = XMLHTTP.responseText
End If
Set XMLHTTP = Nothing
Else
fncStockPrice = 0
End If
End Function
....works for this type of json...
{"symbol":"MNGA","companyName":"MagneGas Corporation","primaryExchange":"NASDAQ Capital Market","sector":"Energy","calculationPrice":"tops","open":1.15,"openTime":1521034200615,"close":0.9369,"closeTime":1520971200288,"high":1.26,"low":1.03,"latestPrice":1.05,"latestSource":"IEX real time price","latestTime":"12:55:30 PM","latestUpdate":1521046530075,"latestVolume":7373180,"iexRealtimePrice":1.05,"iexRealtimeSize":100,"iexLastUpdated":1521046530075,"delayedPrice":1.067,"delayedPriceTime":1521045746335,"previousClose":0.9369,"change":0.1131,"changePercent":0.12072,"iexMarketPercent":0.00783,"iexVolume":57732,"avgTotalVolume":1818685,"iexBidPrice":1.02,"iexBidSize":100,"iexAskPrice":1.08,"iexAskSize":100,"marketCap":106255,"peRatio":-0.04,"week52High":74.55,"week52Low":0..91,"ytdChange":-0.810899182561308}
However, the Yahoo is multilevel and I can't figure out how to get the piece of data I want from it...
{"quoteResponse":{"result":[{"language":"en-US","quoteType":"EQUITY","quoteSourceName":"Nasdaq Real Time Price","currency":"USD","regularMarketPrice":1.05,"regularMarketTime":1521046653,"regularMarketChange":0.11309993,"regularMarketOpen":1.15,"regularMarketDayHigh":1.26,"regularMarketDayLow":1.03,"regularMarketVolume":7377698,"regularMarketChangePercent":12.071718,"regularMarketDayRange":"1.03 - 1.26","fullExchangeName":"NasdaqCM","longName":"MagneGas Corporation","financialCurrency":"USD","averageDailyVolume3Month":1069681,
Not being a coder, I'm struggling here.
Can anyone help me parse this thing?
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
It's something like:
https://query1.finance.yahoo.com/v7/finance/quote?formatted=false&symbols=MMM,IBM https://query1.finance.yahoo.com/v7/finance/quote?formatted=false&symbols=MMM,IBM
...but it's now a JSON file, not a CSV file.
On Fri, Jan 26, 2018 at 7:20 AM, biggntuff@ ...
wrote:
I was using the old Yahoo URL in my VB until they changed. Doesn't work anymore.
Can anyone tell me the underlying URL for the GetYahooPortfolioView function you have setup, please?
Public Function fncStockPrice(Ticker As String, item As String) As Double
Dim strURL As String, strCSV As Double, itemFound As Integer, tag As String
itemFound = 0
If item = "latestPrice" Then
tag = "latestPrice"
itemFound = 1
End If
If itemFound = 1 Then
strURL = "https://api.iextrading.com/1.0/stock/" & Ticker & "/quote/" & tag
Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
XMLHTTP.Open "GET", strURL, False
XMLHTTP.send
If XMLHTTP.responseText = "Unknown symbol" Then
fncStockPrice = 0
Else
fncStockPrice = XMLHTTP.responseText
End If
Set XMLHTTP = Nothing
Else
fncStockPrice = 0
End If
End Function
....works for this type of json...
{"symbol":"MNGA","companyName":"MagneGas Corporation","primaryExchange":"NASDAQ Capital Market","sector":"Energy","calculationPrice":"tops","open":1.15,"openTime":1521034200615,"close":0.9369,"closeTime":1520971200288,"high":1.26,"low":1.03,"latestPrice":1.05,"latestSource":"IEX real time price","latestTime":"12:55:30 PM","latestUpdate":1521046530075,"latestVolume":7373180,"iexRealtimePrice":1.05,"iexRealtimeSize":100,"iexLastUpdated":1521046530075,"delayedPrice":1.067,"delayedPriceTime":1521045746335,"previousClose":0.9369,"change":0.1131,"changePercent":0.12072,"iexMarketPercent":0.00783,"iexVolume":57732,"avgTotalVolume":1818685,"iexBidPrice":1.02,"iexBidSize":100,"iexAskPrice":1.08,"iexAskSize":100,"marketCap":106255,"peRatio":-0.04,"week52High":74.55,"week52Low":0..91,"ytdChange":-0.810899182561308}
However, the Yahoo is multilevel and I can't figure out how to get the piece of data I want from it...
{"quoteResponse":{"result":[{"language":"en-US","quoteType":"EQUITY","quoteSourceName":"Nasdaq Real Time Price","currency":"USD","regularMarketPrice":1.05,"regularMarketTime":1521046653,"regularMarketChange":0.11309993,"regularMarketOpen":1.15,"regularMarketDayHigh":1.26,"regularMarketDayLow":1.03,"regularMarketVolume":7377698,"regularMarketChangePercent":12.071718,"regularMarketDayRange":"1.03 - 1.26","fullExchangeName":"NasdaqCM","longName":"MagneGas Corporation","financialCurrency":"USD","averageDailyVolume3Month":1069681,
Not being a coder, I'm struggling here.
Can anyone help me parse this thing?
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
It's something like:
https://query1.finance.yahoo.com/v7/finance/quote?formatted=false&symbols=MMM,IBM https://query1.finance.yahoo.com/v7/finance/quote?formatted=false&symbols=MMM,IBM
...but it's now a JSON file, not a CSV file.
On Fri, Jan 26, 2018 at 7:20 AM, biggntuff@ ...
wrote:
I was using the old Yahoo URL in my VB until they changed. Doesn't work anymore.
Can anyone tell me the underlying URL for the GetYahooPortfolioVi
Wed Mar 14, 2018 12:37 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I just break it up by "{" into each grouping of data for each symbol and
then parse out what I need from each grouping.
On Wed, Mar 14, 2018 at 12:11 PM, biggntuff@
...
wrote:
>
> This VBA code...
>
> Public Function fncStockPrice(Ticker As String, item As String) As Double
>
> Dim strURL As String, strCSV As Double, itemFound As Integer, tag As String
>
> itemFound = 0
> If item = "latestPrice" Then
> tag = "latestPrice"
> itemFound = 1
>
> End If
>
> If itemFound = 1 Then
>
> strURL = "https://api.iextrading.com/1.0/stock/" & Ticker & "/quote/"
> & tag
> Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
> XMLHTTP.Open "GET", strURL, False
> XMLHTTP.send
>
> If XMLHTTP.responseText = "Unknown symbol" Then
> fncStockPrice = 0
> Else
> fncStockPrice = XMLHTTP.responseText
> End If
>
> Set XMLHTTP = Nothing
>
> Else
>
> fncStockPrice = 0
>
> End If
>
>
> End Function
>
>
>
> ...works for this type of json...
>
>
> -
>
> {"symbol":"MNGA";,"companyName":"MagneGas Corporation","primaryExchange":"NASDAQ Capital Market","sector":"Energy","calculationPrice":"tops";,"open":1.15,"openTime":1521034200615,"close";:0.9369,"closeTime":1520971200288,"high":1.26,"low":1.03,"latestPrice";:1.05,"latestSource":"IEX real time price","latestTime":"12:55:30 PM","latestUpdate":1521046530075,"latestVolume":7373180,"iexRealtimePrice":1.05,"iexRealtimeSize":100,"iexLastUpdated":1521046530075,"delayedPrice":1.067,"delayedPriceTime":1521045746335,"previousClose":0.9369,"change":0.1131,"changePercent":0.12072,"iexMarketPercent":0.00783,"iexVolume":57732,"avgTotalVolume":1818685,"iexBidPrice":1.02,"iexBidSize":100,"iexAskPrice";:1.08,"iexAskSize":100,"marketCap":106255,"peRatio":-0.04,"week52High":74.55,"week52Low":0.91,"ytdChange":-0.810899182561308}
>
> However, the Yahoo is multilevel and I can't figure out how to get the piece of data I want from it...
>
> {"quoteResponse":{"result":[{"language":"en-US","quoteType":"EQUITY","quoteSourceName":"Nasdaq Real Time Price","currency":"USD","regularMarketPrice":1.05,"regularMarketTime":1521046653,"regularMarketChange":0.11309993,"regularMarketOpen":1.15,"regularMarketDayHigh":1.26,"regularMarketDayLow":1.03,"regularMarketVolume":7377698,"regularMarketChangePercent":12.071718,"regularMarketDayRange":"1.03 - 1.26","fullExchangeName":"NasdaqCM","longName":"MagneGas Corporation","financialCurrency":"USD","averageDailyVolume3Month":1069681,
>
>
> Not being a coder, I'm struggling here.
>
> Can anyone help me parse this thing?
>
>
then parse out what I need from each grouping.
On Wed, Mar 14, 2018 at 12:11 PM, biggntuff@
...
wrote:
>
> This VBA code...
>
> Public Function fncStockPrice(Ticker As String, item As String) As Double
>
> Dim strURL As String, strCSV As Double, itemFound As Integer, tag As String
>
> itemFound = 0
> If item = "latestPrice" Then
> tag = "latestPrice"
> itemFound = 1
>
> End If
>
> If itemFound = 1 Then
>
> strURL = "https://api.iextrading.com/1.0/stock/" & Ticker & "/quote/
> & tag
> Set XMLHTTP = CreateObject(
> XMLHTTP.Open "GET"
> XMLHTTP.send
>
> If XMLHTTP.responseTex
> fncStockPrice = 0
> Else
> fncStockPrice = XMLHTTP.responseTex
> End If
>
> Set XMLHTTP = Nothing
>
> Else
>
> fncStockPrice = 0
>
> End If
>
>
> End Function
>
>
>
> ...works for this type of json...
>
>
> -
>
> {"symbol"
>
> However, the Yahoo is multilevel and I can't figure out how to get the piece of data I want from it...
>
> {"quoteRespons
>
>
> Not being a coder, I'm struggling here.
>
> Can anyone help me parse this thing?
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar