9 Messages
Digest #3961
3b
Re: =RCHGetHTMLTable(CONCATENATE( "http://finance.yahoo.com/q/hp?s=^ by "Randy Harmelink" rharmelink
Messages
Tue Apr 11, 2017 4:43 am (PDT) . Posted by:
bob_15861586
Randy,
I deleted the old version of SMF on the disk and repeated the installation process. It appears to have worked. The Excel expression =RCHGetElementNumber("Version") now returns the string:
Stock Market Functions add-in, Version 2.1.2017.03.15 (C:\SMF Add-In; Windows (32-bit) NT :.00; 15.0; ; ; 1)
Thank you very much.
Bob
I deleted the old version of SMF on the disk and repeated the installation process. It appears to have worked. The Excel expression =RCHGetElementNumbe
Stock Market Functions add-in, Version 2.1.2017.03.
Thank you very much.
Bob
Tue Apr 11, 2017 8:02 am (PDT) . Posted by:
"Paul Austin" jpaulaustin
Randy,
I ran across this post and it is quite helpful.
Is there any way to bring this data (particularly the asset allocation)
into Excel in a reasonable format?
Paul
On Sat, Apr 1, 2017 at 3:05 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> It looks like they've changed the JSON calls.
>
> Using my HttpFox extension in FireFox, I found these JSON calls when I
> display the NBB web page:
>
> http://www.cefconnect.com/api/v3/distributionhistory/fund/NB
> B/04-01-2016/04-01-2017
> http://www.cefconnect.com/api/v3/PriceHistory/NBB/1M/03-01-2017
> http://www.cefconnect.com/api/v3/pricinghistory/NBB/1Y
> http://www.cefconnect.com/api/v3/distributioncharter/fund/NBB/All
> http://www.cefconnect.com/api/v3/distributioncharter/fund/NBB/1Y
> http://www.cefconnect.com/api/v3/DiscountCharter/fund/NBB/1Y
> http://www.cefconnect.com/api/v3/performance/calendar/NBB
> http://www.cefconnect.com/api/v3/performance/annualized/NBB
> http://www.cefconnect.com/api/v3/assetallocation/NBB
> http://www.cefconnect.com/api/v3/callexposure/NBB
> http://www.cefconnect.com/api/v3/creditquality/NBB
> http://www.cefconnect.com/api/v3/maturity/NBB
>
> The first contains your distributions. I included the others in case you
> wanted to explore them.
>
>
> On Sat, Apr 1, 2017 at 7:32 AM, scwalker1986@
> ...wrote:
>
>>
>> A thousands thanks for good work you do, and the kindness you show to
>> others. I have used code on and off over the years you have enabled, and
>> I am grateful to have benefited from your work and others that have built
>> templates from it. I wish you the best with your health.
>>
>> I am working improving my CEF investing, and getting to the true income
>> distribution (vs the gross distribution) is important. I came across the
>> your post from Sept 2013 on getting to this information (
>> https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/23587),
>> but it appears that code no longer works, while on the surface the site did
>> not change since then.
>>
>> As an example, longer at NBB http://www.cefconnect.com/fund/NBB
>> I wanted to get to at least the latest "Income" value of .103 (in this
>> case same as distribution, but in others different). Looking at the
>> source page, the cusip is 67074C103
>> Declared DatePayable DateEx DateDistrib AmountIncomeLong GainShort Gain
>> ROC
>> 3/1/2017 4/3/2017 3/13/2017 $0.1030
>>
>> $0.1030 $0.0000 $0.0000
>>
>>
>>
>> So then this code would be what would have worked before
>> =RCHGetWebData("http://www.cefconnect.com/Resources/TableDat
>> a/?Type=DistributionHistory¶m0=1/1/1980¶m1=12/31/202
>> 0&Cusip=67074C103")
>>
>> But now it gets an error.
>>
>> Any ideas?
>>
>>
>
>
I ran across this post and it is quite helpful.
Is there any way to bring this data (particularly the asset allocation)
into Excel in a reasonable format?
Paul
On Sat, Apr 1, 2017 at 3:05 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> It looks like they've changed the JSON calls.
>
> Using my HttpFox extension in FireFox, I found these JSON calls when I
> display the NBB web page:
>
> http://www.cefconnect.com/api/v3/distributionhistory/fund/NB
> B/04-01-2016/04-01-2017
> http://www.cefconnect.com/api/v3/PriceHistory/NBB/1M/03-01-2017
> http://www.cefconnect.com/api/v3/pricinghistory/NBB/1Y
> http://www.cefconnect.com/api/v3/distributioncharter/fund/NBB/All
> http://www.cefconnect.com/api/v3/distributioncharter/fund/NBB/1Y
> http://www.cefconnect.com/api/v3/DiscountCharter/fund/NBB/1Y
> http://www.cefconnect.com/api/v3/performance/calendar/NBB
> http://www.cefconnect.com/api/v3/performance/annualized/NBB
> http://www.cefconnect.com/api/v3/assetallocation/NBB
> http://www.cefconnect.com/api/v3/callexposure/NBB
> http://www.cefconnect.com/api/v3/creditquality/NBB
> http://www.cefconnect.com/api/v3/maturity/NBB
>
> The first contains your distributions. I included the others in case you
> wanted to explore them.
>
>
> On Sat, Apr 1, 2017 at 7:32 AM, scwalker1986@
> ...wrote:
>
>>
>> A thousands thanks for good work you do, and the kindness you show to
>> others. I have used code on and off over the years you have enabled, and
>> I am grateful to have benefited from your work and others that have built
>> templates from it. I wish you the best with your health.
>>
>> I am working improving my CEF investing, and getting to the true income
>> distribution (vs the gross distribution) is important. I came across the
>> your post from Sept 2013 on getting to this information (
>> https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/23587),
>> but it appears that code no longer works, while on the surface the site did
>> not change since then.
>>
>> As an example, longer at NBB http://www.cefconnect.com/fund/NBB
>> I wanted to get to at least the latest "Income" value of .103 (in this
>> case same as distribution, but in others different). Looking at the
>> source page, the cusip is 67074C103
>> Declared DatePayable DateEx DateDistrib AmountIncomeLong GainShort Gain
>> ROC
>> 3/1/2017 4/3/2017 3/13/2017 $0.1030
>>
>> $0.1030 $0.0000 $0.0000
>>
>>
>>
>> So then this code would be what would have worked before
>> =RCHGetWebData("http://www.cefconnect.com/Resources/TableDat
>> a/?Type=Distributio
>> 0&Cusip=67074C103&q
>>
>> But now it gets an error.
>>
>> Any ideas?
>>
>>
>
>
Tue Apr 11, 2017 9:48 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You would need to use RCHGetWebData() to pull in the data, smfStrExtr() to
extract out the data, and maybe smfConvertData() to convert text data to
numeric.
An example of extracting the number of full-time employees from a Yahoo
JSON query:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/30448
It may no longer work. I can't try it out. But it should give you some idea
of the function usage necessary to do an extraction from JSON output.
On Tue, Apr 11, 2017 at 8:01 AM, Paul Austin paul@
...wrote:
>
> I ran across this post and it is quite helpful.
> Is there any way to bring this data (particularly the asset allocation)
> into Excel in a reasonable format?
> Paul
>
> On Sat, Apr 1, 2017 at 3:05 PM, Randy Harmelink rharmelink@gmail.com
> [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>>
>>
>> It looks like they've changed the JSON calls.
>>
>> Using my HttpFox extension in FireFox, I found these JSON calls when I
>> display the NBB web page:
>>
>> http://www.cefconnect.com/api/v3/distributionhistory/fund/NB
>> B/04-01-2016/04-01-2017
>> http://www.cefconnect.com/api/v3/PriceHistory/NBB/1M/03-01-2017
>> http://www.cefconnect.com/api/v3/pricinghistory/NBB/1Y
>> http://www.cefconnect.com/api/v3/distributioncharter/fund/NBB/All
>> http://www.cefconnect.com/api/v3/distributioncharter/fund/NBB/1Y
>> http://www.cefconnect.com/api/v3/DiscountCharter/fund/NBB/1Y
>> http://www.cefconnect.com/api/v3/performance/calendar/NBB
>> http://www.cefconnect.com/api/v3/performance/annualized/NBB
>> http://www.cefconnect.com/api/v3/assetallocation/NBB
>> http://www.cefconnect.com/api/v3/callexposure/NBB
>> http://www.cefconnect.com/api/v3/creditquality/NBB
>> http://www.cefconnect.com/api/v3/maturity/NBB
>>
>> The first contains your distributions. I included the others in case you
>> wanted to explore them.
>>
>>
>>
extract out the data, and maybe smfConvertData() to convert text data to
numeric.
An example of extracting the number of full-time employees from a Yahoo
JSON query:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/30448
It may no longer work. I can't try it out. But it should give you some idea
of the function usage necessary to do an extraction from JSON output.
On Tue, Apr 11, 2017 at 8:01 AM, Paul Austin paul@
...wrote:
>
> I ran across this post and it is quite helpful.
> Is there any way to bring this data (particularly the asset allocation)
> into Excel in a reasonable format?
> Paul
>
> On Sat, Apr 1, 2017 at 3:05 PM, Randy Harmelink rharmelink@gmail.com
> [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>>
>>
>> It looks like they've changed the JSON calls.
>>
>> Using my HttpFox extension in FireFox, I found these JSON calls when I
>> display the NBB web page:
>>
>> http://www.cefconnect.com/api/v3/distributionhistory/fund/NB
>> B/04-01-2016/04-01-2017
>> http://www.cefconnect.com/api/v3/PriceHistory/NBB/1M/03-01-2017
>> http://www.cefconnect.com/api/v3/pricinghistory/NBB/1Y
>> http://www.cefconnect.com/api/v3/distributioncharter/fund/NBB/All
>> http://www.cefconnect.com/api/v3/distributioncharter/fund/NBB/1Y
>> http://www.cefconnect.com/api/v3/DiscountCharter/fund/NBB/1Y
>> http://www.cefconnect.com/api/v3/performance/calendar/NBB
>> http://www.cefconnect.com/api/v3/performance/annualized/NBB
>> http://www.cefconnect.com/api/v3/assetallocation/NBB
>> http://www.cefconnect.com/api/v3/callexposure/NBB
>> http://www.cefconnect.com/api/v3/creditquality/NBB
>> http://www.cefconnect.com/api/v3/maturity/NBB
>>
>> The first contains your distributions. I included the others in case you
>> wanted to explore them.
>>
>>
>>
Tue Apr 11, 2017 10:53 am (PDT) . Posted by:
gwlunceford
I have been using the following formula to pull prior year and prior month Dow Jones close numbers.
=RCHGetHTMLTable(CONCATENATE( "http://finance.yahoo.com/q/hp?s=^DJI&a=", MONTH(Details!N7)-1,"&b=",DAY(Details!N7),"&c=",YEAR(Details!N7),"&d=", MONTH(Details!N7)-1,"&e=",DAY(Details!N7),"&f=",YEAR(Details!N7),"&g=d"), "yfnc_datamodoutline1",-1,"",1)
It stop working in the last few days? Would anyone happen to know if Yahoo changed where the data is located?
Thanks,
Gary
=RCHGetHTMLTable(CONCATENATE( "http://finance.yahoo.com/q/hp?s=^DJI&a="
It stop working in the last few days? Would anyone happen to know if Yahoo changed where the data is located?
Thanks,
Gary
Tue Apr 11, 2017 12:23 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
There is no longer an HTML table of data in the source code of the web
page. Yahoo has been changing things so that the web pages are created
dynamically from JSON data. The JSON call for yesterday's data looks
something like:
https://query1.finance.yahoo.com/v8/finance/chart/
^DJI?period1=1491807600&period2=1491807600&interval=1d&events=div|split
...where the "period" values are UNIX values (# of seconds since
01-01-1970), or:
=86400*(DATE(2017,4,10)-DATE(1970,1,1))
I have no access to EXCEL in the near future...?
Have you tried using the RCHGetYahooHistory() function? Yahoo was
preventing it from using ^DJI several years ago, but I think the data is
available these days?
You may even be able to use the smfPricesByDates() function and get both
adjusted closing prices at the same time.
On Tue, Apr 11, 2017 at 10:53 AM, gwlunceford@
...wrote:
> I have been using the following formula to pull prior year and prior month
> Dow Jones close numbers.
>
> =RCHGetHTMLTable(CONCATENATE( "http://finance.yahoo.com/q/hp?s=^DJI&a=",
> MONTH(Details!N7)-1,"&b=",DAY(Details!N7),"&c=",YEAR(Details!N7),"&d=",
> MONTH(Details!N7)-1,"&e=",DAY(Details!N7),"&f=",YEAR(Details!N7),"&g=d"),
> "yfnc_datamodoutline1",-1,"",1)
>
> It stop working in the last few days? Would anyone happen to know if Yahoo
> changed where the data is located?
>
>
page. Yahoo has been changing things so that the web pages are created
dynamically from JSON data. The JSON call for yesterday's data looks
something like:
https://query1.finance.yahoo.com/v8/finance/chart/
^DJI?period1=1491807600&period2=1491807600&interval=1d&events=div|split
...where the "period" values are UNIX values (# of seconds since
01-01-1970), or:
=86400*(DATE(2017,4,10)-DATE(1970,1,1))
I have no access to EXCEL in the near future...?
Have you tried using the RCHGetYahooHistory() function? Yahoo was
preventing it from using ^DJI several years ago, but I think the data is
available these days?
You may even be able to use the smfPricesByDates() function and get both
adjusted closing prices at the same time.
On Tue, Apr 11, 2017 at 10:53 AM, gwlunceford@
...wrote:
> I have been using the following formula to pull prior year and prior month
> Dow Jones close numbers.
>
> =RCHGetHTMLTable(CONCATENATE( "http://finance.yahoo.com/q/hp?s=^DJI&a="
> MONTH(Details!
> MONTH(Details!
> "yfnc_datamodo
>
> It stop working in the last few days? Would anyone happen to know if Yahoo
> changed where the data is located?
>
>
Tue Apr 11, 2017 11:58 am (PDT) . Posted by:
"Tommy Atkins" tomatkins
How do I get quotes for ^DJI?
I've tried
=RCHGetYahooQuotes("^DJI","l1")
And get "N/A" although other indices work fines, such as:
=RCHGetYahooQuotes("^GSPC,"l1") or
=RCHGetYahooQuotes("^IXIC";,"l1")
Thanx,
TommyGuy
I've tried
=RCHGetYahooQuotes(
And get "N/A" although other indices work fines, such as:
=RCHGetYahooQuotes(
=RCHGetYahooQuotes(
Thanx,
TommyGuy
Tue Apr 11, 2017 12:28 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The RCHGetYahooQuotes() function cannot return any data on Dow Jones
indexes, because Dow Jones licensing doesn't allow Yahoo to provide the
data in a CSV file.
I won't have access to EXCEL in the near future...
On Tue, Apr 11, 2017 at 11:18 AM, 'Tommy Atkins' tommyatkins@
...wrote:
>
> How do I get quotes for ^DJI?
>
> I've tried
>
> =RCHGetYahooQuotes("^DJI","l1")
>
> And get "N/A" although other indices work fines, such as:
>
> =RCHGetYahooQuotes("^GSPC,"l1") or
>
> =RCHGetYahooQuotes("^IXIC","l1")
>
>
>
>
indexes, because Dow Jones licensing doesn't allow Yahoo to provide the
data in a CSV file.
I won't have access to EXCEL in the near future...
On Tue, Apr 11, 2017 at 11:18 AM, 'Tommy Atkins' tommyatkins@
...wrote:
>
> How do I get quotes for ^DJI?
>
> I've tried
>
> =RCHGetYahooQuotes("^DJI","l1"
>
> And get "N/A" although other indices work fines, such as:
>
> =RCHGetYahooQuotes("^GSPC,"
>
> =RCHGetYahooQuotes("^IXIC","l1"
>
>
>
>
Tue Apr 11, 2017 6:45 pm (PDT) . Posted by:
"Andrew Hills" andrew.hills@hunterfield.com.au
Thanks Randy. I'm struggling to get that to work – I'm not as proficient as you!
I have stock codes in column R and ='RCHGetTableCell((" <http://finance.yahoo.com/q/ce?s=> http://finance.yahoo.com/q/ce?s="&R3),1,">Event",,,,1) in column S. I open the SMF Add in file and the upcoming event dates are updated in column S. What are you suggesting I do with <https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=assetProfile,secFilings,calendarEvents> https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=assetProfile,secFilings,calendarEvents ??
Thanks and Regards,
Andrew
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Tuesday, April 11, 2017 6:02 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Did Yahoo make some massive change within past day
The JSON data used to populate the page is here:
https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=assetProfile,secFilings,calendarEvents
On Mon, Apr 10, 2017 at 12:49 PM, 'Andrew Hills' andrew.hills@
...wrote:
I noticed this extraction doesn't work as well. Any help would be much appreciated.
='RCHGetTableCell(("http://finance.yahoo.com/q/ce?s="&R3),1,">Event",,,,1)
I have stock codes in column R and ='RCHGetTableCell((" <http://finance.yahoo.com/q/ce?s=> http://finance.yahoo.com/q/ce?s="&R3),1,">Event",,,,1) in column S. I open the SMF Add in file and the upcoming event dates are updated in column S. What are you suggesting I do with <https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=assetProfile,secFilings,calendarEvents> https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=assetProfile,secFilings,calendarEvents ??
Thanks and Regards,
Andrew
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Tuesday, April 11, 2017 6:02 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Did Yahoo make some massive change within past day
The JSON data used to populate the page is here:
https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=assetProfile,secFilings,calendarEvents
On Mon, Apr 10, 2017 at 12:49 PM, 'Andrew Hills' andrew.hills@
...wrote:
I noticed this extraction doesn't work as well. Any help would be much appreciated.
='RCHGetTableCell(("http://finance.yahoo.com/q/ce?s="&R3),1,
Tue Apr 11, 2017 6:47 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Unfortunately, I won't have EXCEL available for a few weeks...
On Tue, Apr 11, 2017 at 6:45 PM, 'Andrew Hills' andrew.hills@
...wrote:
>
> Thanks Randy. I'm struggling to get that to work – I'm not as proficient
> as you!
>
>
>
> I have stock codes in column R and ='RCHGetTableCell(("http://
> finance.yahoo.com/q/ce?s="&R3),1,">Event",,,,1) in column S. I open the
> SMF Add in file and the upcoming event dates are updated in column S. What
> are you suggesting I do with https://query1.finance.yahoo.
> com/v10/finance/quoteSummary/AAPL?modules=assetProfile,
> secFilings,calendarEvents ??
>
>
>
>
On Tue, Apr 11, 2017 at 6:45 PM, 'Andrew Hills' andrew.hills@
...wrote:
>
> Thanks Randy. I'm struggling to get that to work – I'm not as proficient
> as you!
>
>
>
> I have stock codes in column R and ='RCHGetTableCell(("http://
> finance.yahoo.com/q/ce?s="&R3),1,">Event",,,,1) in column S. I open the
> SMF Add in file and the upcoming event dates are updated in column S. What
> are you suggesting I do with https://query1.finance.yahoo.
> com/v10/finance/
> secFilings,calendar
>
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar