Jumat, 26 Mei 2017

[smf_addin] Digest Number 4035[2 Attachments]

15 Messages

Digest #4035
1a
Re: Loading of the Add-in by Ecel 2013 by mthmu3uhchaa4cu3fj53vppa7f2ab7cx7srhngvp
4a
Re: smfPricesByDates not working by rob.rudy@sbcglobal.net
4b
Re: smfPricesByDates not working by "Randy Harmelink" rharmelink
4c
Re: smfPricesByDates not working by rob.rudy@sbcglobal.net
4d
Re: smfPricesByDates not working by rob.rudy@sbcglobal.net
5b
6b
Re: Weekly Quote Issue [2 Attachments] by "Randy Harmelink" rharmelink
7b
Re: Historical price data - Yahoo URL for VBA by "Randy Harmelink" rharmelink

Messages

Fri May 26, 2017 11:42 am (PDT) . Posted by:

marsh_gerda

Randy,

thank you, thank you, thank you, thank you! My complex MFI tracking workbooks were erring out. But your beta SMFs have done the trick.


You are the best!


Marshall

Fri May 26, 2017 1:01 pm (PDT) . Posted by:

jtassielli2003

Hi Randy,


I just wish to extract a single cell, E4: 91.49.


I used


http://performance.morningstar.com/Performance/stock/exportStockPrice.action?pd=10y&freq=a&t=PM http://performance.morningstar.com/Performance/stock/exportStockPrice.action?pd=10y&freq=a&t=PM


thanks




Date Open High Low Close Volume 5/25/2017 91.49 119.78 89.97 119.48 4,226,889 12/30/2016 87.91 104.2 84.46 91.49 4,470,395













"#28540 May 31, 2015

Try something like:

=smfGetCSVFile("h**p://performance.morningstar.com/Performance/stock/exportStockPrice.action?pd=10y&freq=a&t=MMM")
...or did you have a different table in mind?"


Fri May 26, 2017 1:26 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Try:

=smfConvertData(smfWord(RCHGetWebData("
http://performance.morningstar.com/Performance/stock/exportStockPrice.action?pd=10y&freq=a&t=PM
","12/30/2016,",100),5,","))

Or, if you know where it will be on the returned file:

=INDEX(smfGetCSVFile("
http://performance.morningstar.com/Performance/stock/exportStockPrice.action?pd=10y&freq=a&t=PM
",,4,5),4,5)

On Fri, May 26, 2017 at 1:01 PM, jtassielli2003@
​...wrote:

>
> I just wish to extract a single cell, E4: 91.49.
>
> I used
>
> http://performance.morningstar.com/Performance/
> stock/exportStockPrice.action?pd=10y&freq=a&t=PM
>
> thanks
>
> Date Open High Low Close Volume
> 5/25/2017 91.49 119.78 89.97 119.48 4,226,889
> 12/30/2016 87.91 104.2 84.46 91.49 4,470,395
>
>
>
>

Fri May 26, 2017 2:03 pm (PDT) . Posted by:

rob.rudy@sbcglobal.net

Hi,
I use smfPricesbyDates occasionally for a few years now. It last worked for me in mid-March. Yesterday, I discovered it had stopped working. I just get a #NAME?. After reading this thread, I updated the files RCH_Stock_Market_Functions.xla and RCHGETElementNumber now dated 5/3/17 and 5/2/17.

Any suggestions would be appreciated.

Fri May 26, 2017 2:23 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The main topic of the Yahoo group for the last few weeks has been about the
big change in Yahoo historical quotes. Their CSV file is no longer
available to the add-in. I did manage a work-around, but it's in
experimental *beta* mode, so there is no official release. See:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32237
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32343

​However, a #NAME? error would usually mean the add-in isn't active at all.
Check your add-in manager to verify the add-in is loaded. You can easily
check the version of the add-in, if it's active, with:

=RCHGetElementNumber("Version")

If EXCEL isn't loading the add-in, it can be a result of a Microsoft
security update from last year. See:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/30568

On Fri, May 26, 2017 at 1:57 PM, rob.rudy@
​...wrote:

>
> I use smfPricesbyDates occasionally for a few years now. It last worked
> for me in mid-March. Yesterday, I discovered it had stopped working. I
> just get a #NAME?. After reading this thread, I updated the files
> RCH_Stock_Market_Functions.xla and RCHGETElementNumber now dated 5/3/17 and
> 5/2/17.
>
> Any suggestions would be appreciated.
>

Fri May 26, 2017 3:16 pm (PDT) . Posted by:

rob.rudy@sbcglobal.net

Thanks for the quick reply. I unblocked the add-in, but still can't get prices. The cells now display #NA. The call to GetElement Number now returns ,,,

Stock Market Functions add-in, Version 2.1.2017.05.03 (C:\Users\Username\smf stock data; Windows (32-bit) NT 6.01; 14.0; ; ; 1)"

Guess I will wait for official release?

Fri May 26, 2017 3:34 pm (PDT) . Posted by:

rob.rudy@sbcglobal.net

Randy - Update - downloaded the updated beta you posted and it is now working for me. Thanks so much - this saves me a lot of manual labor pulling prices. . Will check back again for when next release is out. Your work here is greatly appreciated.

Fri May 26, 2017 4:04 pm (PDT) . Posted by:

"Jim Moody"

Good afternoon,

I've been looking at the spreadsheet called smfGetOptionQuotes-Template-CC-List-Returns and finding it very helpful. Can you help me out with a stupid question?

I know what the column for RIU and RIE is calculating, very helpful, but I can't figure out what the column headers RIU and RIE stand for. Small thing but driving me bonkers. Can't find it on Google.

Can you help?

Jim Moody

Fri May 26, 2017 4:07 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Basically:

RIU = Return If Unchanged price to expiration date
RIE = Return If Exercised at strike price

So they should be the same if the call is written ITM.

On Fri, May 26, 2017 at 3:50 PM, 'Jim Moody' jimmymoodz@
​...wrote:

>
>
> I've been looking at the spreadsheet called smfGetOptionQuotes-Template-CC-List-Returns
> and finding it very helpful. Can you help me out with a stupid question?
>
>
>
> I know what the column for RIU and RIE is calculating, very helpful, but I
> can't figure out what the column headers RIU and RIE stand for. Small
> thing but driving me bonkers. Can't find it on Google.
>
>
>
> Can you help?
>
>
>

Fri May 26, 2017 4:12 pm (PDT) . Posted by:

"Jim Moody"

Dang. You rock with the quick replies. Thanks!

On Fri, May 26, 2017 at 4:07 PM Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> Basically:
>
> RIU = Return If Unchanged price to expiration date
> RIE = Return If Exercised at strike price
>
> So they should be the same if the call is written ITM.
>
> On Fri, May 26, 2017 at 3:50 PM, 'Jim Moody' jimmymoodz@
> ​...wrote:
>
>
>>
>> I've been looking at the spreadsheet called
>> smfGetOptionQuotes-Template-CC-List-Returns and finding it very helpful.
>> Can you help me out with a stupid question?
>>
>>
>>
>> I know what the column for RIU and RIE is calculating, very helpful, but
>> I can't figure out what the column headers RIU and RIE stand for. Small
>> thing but driving me bonkers. Can't find it on Google.
>>
>>
>>
>> Can you help?
>>
>>
>>
>
>
>

Fri May 26, 2017 6:12 pm (PDT) . Posted by:

mjlwis



Randy, first of all thank you for all your effort and the product you share with us! It has worked great for me in the past and in general your recent changes have been working well for me. I have run into a problem in collecting weekly quotes. In the past I was able to collect the quotes on Friday evening. Now when I use =smfGetYahooHistory(A13,$B$1,$B$4,"w","dohlcv",0) B1 is 5/22/17 and B2 is 5/26/17.

I get:
<Ticker>
Date
<Open>
<High>
<Low>
<Close>
Vol
AA
5/26/2017
32.87
33.36
32.87
33.27
2410586

If I give the array two rows I get an entry for both the 5/26/17 and 5/22/17. The 5/22/17 is the correct entry for the week and of course, the only one I want. I have attached the responses from the yahoo.finance.com website. It also gives two entries for the weekly data. Is this an issue that yahoo would have to correct or something you can change. Also, last week after trading on the 22nd, both the site and function returned the correct weekly entry for the week before - May 15-19, 2017.

Thanks again for all your efforts!

Mike














































Attachment(s) from
2 of 2 Photo(s)

Fri May 26, 2017 6:37 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I set it up to return whatever Yahoo returned. If you want it to just get
the second row, you could do:

=INDEX(smfGetYahooHistory(A3,$B$3,$C$3,"w","dohlcv&quot;,0),2)

However, my experience has been that Yahoo is inconsistent with that first
row of data being there. And their weekly data will not be adjusted
properly for dividends that occur during the week. Instead, I would suggest
using:

=smfPricesBetween(A3,C3-6,C3,"010204060809")

It would extract prices from smfGetYahooHistory() AFTER dividend
adjustments are done. Check out JNJ to see differences. They paid out a
$0.84 dividend this week. And, actually, for that last parameter, I would
start with "07" rather than "01", because I'd prefer to see the ending date:

=smfPricesBetween(A3,C3-6,C3,"070204060809")

But that's just me.

On Fri, May 26, 2017 at 6:12 PM, mjlstuff@
​...wrote:

>
> Randy, first of all thank you for all your effort and the product you
> share with us! It has worked great for me in the past and in general
> your recent changes have been working well for me. I have run into a
> problem in collecting weekly quotes. In the past I was able to collect
> the quotes on Friday evening. Now when I use
> =smfGetYahooHistory(A13,$B$1,$B$4,"w","dohlcv",0) B1 is 5/22/17 and B2
> is 5/26/17.
>
> I get:
>
> <Ticker>
>
> Date
>
> <Open>
>
> <High>
>
> <Low>
>
> <Close>
>
> Vol
>
> AA
>
> 5/26/2017
>
> 32.87
>
> 33.36
>
> 32.87
>
> 33.27
>
> 2410586
>
>
>
> If I give the array two rows I get an entry for both the 5/26/17 and
> 5/22/17. The 5/22/17 is the correct entry for the week and of course,
> the only one I want. I have attached the responses from the
> yahoo.finance.com website. It also gives two entries for the weekly data.
> Is this an issue that yahoo would have to correct or something you can
> change. Also, last week after trading on the 22nd, both the site and
> function returned the correct weekly entry for the week before - May 15-19,
> 2017.
>
>
>
> Thanks again for all your efforts!
>
>
>

Fri May 26, 2017 6:34 pm (PDT) . Posted by:

samil_beret

Hi Randy


For a couple of years, I have been using a simple VBA code to download Yahoo stock price cvs files using the following statement: qurl = "URL;http://ichart.finance.yahoo.com/table.csv?s=" + StockTicker + "&a=" + StartMonth + "&b=" + StartDay + "&c=" + StartYear + "&d=" + EndMonth + "&e=" + EndDay + "&f=" + EndYear + "&g=" + freq + "&ignore=.csv"



[all the variables are defined in the code previous to this statement]. But this URL does not seem to work anymore and I suspect it is due to all recent Yahoo changes.


Is there a way to modify this statement to make t work with recent Yahoo changes? Thanks for your help.


Best Regards

Fri May 26, 2017 6:46 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry, but Yahoo has unplugged that URL. And they've indicated they have no
plans to restore it.

The new URL to get a CSV file requires both a cookie and a "crumb"
variable, so the add-in can no longer get it. Instead, I changed my
functions to extract the data from the web page itself -- it's within the
web page as JSON data. A lot of providers seem to be going to JSON-sourced
data, so I ended up adding some JSON extraction routines to the add-in.

I couldn't even get to the proper URL with the EXCEL Web Query dialog. It
gives me a "page not found" type of error, so I'm not even sure a query
could be set up on the web page. I was trying to get to that web page to
create the cookie for the add-in. But I don't see a way to create it for
the query either. I suppose you could try the new URL in your VBA query, to
see if it works. With the add-in, I get an "unauthorized access" type of
error.

On Fri, May 26, 2017 at 6:34 PM, sberet@
​...wrote:

> For a couple of years, I have been using a simple VBA code to download
> Yahoo stock price cvs files using the following statement: qurl = "URL;
> http://ichart.finance.yahoo.com/table.csv?s=" + StockTicker + "&a=" +
> StartMonth + "&b=" + StartDay + "&c=" + StartYear + "&d=" + EndMonth +
> "&e=" + EndDay + "&f=" + EndYear + "&g=" + freq + "&ignore=.csv"
>
> [all the variables are defined in the code previous to this statement].
> But this URL does not seem to work anymore and I suspect it is due to all
> recent Yahoo changes.
>
> Is there a way to modify this statement to make t work with recent Yahoo
> changes? Thanks for your help.
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 18.46

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)