6 Messages
Digest #4579
Messages
Wed Jul 17, 2019 7:12 am (PDT) . Posted by:
seongkook_berry
I'm trying to get the historical monthly high and low stock prices and dividend data for a large number of companies. I've been using RCHGetYahooHistory but I've notice that it appears to give me adjusted prices. For example, according to yahoo, ENBL's high price for February 2019 was 16.49 and its dividend was 0.318. However, when I use RCHGetYahooHistory I get 16.14 for the high price. Adjusting for the dividend you get 16.17 so I not entirely sure why I'm getting a different number. Here's the formula I used:
=RCHGetYahooHistory("ENBL";,YEAR($A7),MONTH($A7),DAY($A7),YEAR($B7),MONTH($B7),DAY($B7),"M","H",0)
I also use this function to get my dividend data but you have to create an array. Creating a array adds extra steps to my spreadsheet. All I want is the dividend during a specified period. Is there another way to get this information? Basically, I want the data from yahoo's historical data (see the attachment for an example).
=RCHGetYahooHistory
I also use this function to get my dividend data but you have to create an array. Creating a array adds extra steps to my spreadsheet. All I want is the dividend during a specified period. Is there another way to get this information? Basically, I want the data from yahoo's historical data (see the attachment for an example).
Wed Jul 17, 2019 9:18 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
IMO, it would make no sense to get an unadjusted high price from a period
of time. A 1:20 stock split would create a new high price. Or a run-up
after a dividend might fail to create a new high. The prices during the
period need to be adjusted in order to have the proper context to each
other.
I don't think you understand adjustments. They are proportional, not
subtractive. If you were to subtract, older adjusted prices could end up
being negative.
And, BTW, monthly prices from Yahoo are garbage, as far as adjustments go.
They adjust them based on the end-of-month price instead of the day-of
price, so any month that has a dividend will have an incorrect adjustment
for that month.
When I want dividends paid during a period, I just use something like:
=SUM(INDEX(smfGetYahooHistory("ENBL","2/1/2019","2/28/2019","v",,,,100,2),,2))
I would suggest using smfGetYahooHistory() as it's a replacement for
RCHGetYahooHistory(), which just exists for some level of backward
compatibility.
On Wed, Jul 17, 2019 at 7:15 AM seongkook_berry@... wrote:
> I'm trying to get the historical monthly high and low stock prices and
> dividend data for a large number of companies. I've been using
> RCHGetYahooHistory but I've notice that it appears to give me adjusted
> prices. For example, according to yahoo, ENBL's high price for February
> 2019 was 16.49 and its dividend was 0.318. However, when I use
> RCHGetYahooHistory I get 16.14 for the high price. Adjusting for the
> dividend you get 16.17 so I not entirely sure why I'm getting a different
> number. Here's the formula I used:
>
>
> =RCHGetYahooHistory("ENBL";,YEAR($A7),MONTH($A7),DAY($A7),YEAR($B7),MONTH($B7),DAY($B7),"M","H",0)
>
> I also use this function to get my dividend data but you have to create an
> array. Creating a array adds extra steps to my spreadsheet. All I want is
> the dividend during a specified period. Is there another way to get this
> information? Basically, I want the data from yahoo's historical data (see
> the attachment for an example).
>
>
>
of time. A 1:20 stock split would create a new high price. Or a run-up
after a dividend might fail to create a new high. The prices during the
period need to be adjusted in order to have the proper context to each
other.
I don't think you understand adjustments. They are proportional, not
subtractive. If you were to subtract, older adjusted prices could end up
being negative.
And, BTW, monthly prices from Yahoo are garbage, as far as adjustments go.
They adjust them based on the end-of-month price instead of the day-of
price, so any month that has a dividend will have an incorrect adjustment
for that month.
When I want dividends paid during a period, I just use something like:
=SUM(INDEX(smfGetYa
I would suggest using smfGetYahooHistory(
RCHGetYahooHistory(
compatibility.
On Wed, Jul 17, 2019 at 7:15 AM seongkook_berry@
> I'm trying to get the historical monthly high and low stock prices and
> dividend data for a large number of companies. I've been using
> RCHGetYahooHistory but I've notice that it appears to give me adjusted
> prices. For example, according to yahoo, ENBL's high price for February
> 2019 was 16.49 and its dividend was 0.318. However, when I use
> RCHGetYahooHistory I get 16.14 for the high price. Adjusting for the
> dividend you get 16.17 so I not entirely sure why I'm getting a different
> number. Here's the formula I used:
>
>
> =RCHGetYahooHistory
>
> I also use this function to get my dividend data but you have to create an
> array. Creating a array adds extra steps to my spreadsheet. All I want is
> the dividend during a specified period. Is there another way to get this
> information? Basically, I want the data from yahoo's historical data (see
> the attachment for an example).
>
>
>
Wed Jul 17, 2019 9:51 am (PDT) . Posted by:
a.bereziuk
Randy, I have tested the macro smfUpdateDownloadTable() with this template: smfUpdateDownloadTable-Sample.xls. It worked only with the webpage extraction function in the template: RCHGetTableCell and GetElementNumber. It did not work with Yahoo functions smfGetYahooPortfolioView("~~~~~";"15") and AVERAGE(smfGetYahooHistory("~~~~~";;;;"c";0;;50;1)) above the column titles.
Wed Jul 17, 2019 11:36 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Please NEVER use smfGetYahooPortfolioView() to get numerous individual data
items. Not only is it a waste of time for you, but it generates numerous
requests for Yahoo data. The last thing I want is for Yahoo to turn off the
ability of the add-in to access such data.I may have to add something to
that function to prevent such usage.
Having said that, the two formulas worked fine for me using
smfUpdateDownloadTable:
smfGetYahooPortfolioView("~~~~~","15")
AVERAGE(smfGetYahooHistory("~~~~~",,,,"c",0,,50,1))
*Ticker*
MMM $175.29 $169.58
On Wed, Jul 17, 2019 at 9:51 AM a.bereziuk@... wrote:
>
> Randy, I have tested the macro smfUpdateDownloadTable() with this
> template: smfUpdateDownloadTable-Sample.xls. It worked only with the
> webpage extraction function in the template: RCHGetTableCell and
> GetElementNumber. It did not work with Yahoo functions smfGetYahooPortfolioView("~~~~~";"15")
> and AVERAGE(smfGetYahooHistory("~~~~~";;;;"c";0;;50;1)) above the column
> titles.
>
>
items. Not only is it a waste of time for you, but it generates numerous
requests for Yahoo data. The last thing I want is for Yahoo to turn off the
ability of the add-in to access such data.I may have to add something to
that function to prevent such usage.
Having said that, the two formulas worked fine for me using
smfUpdateDownloadTa
smfGetYahooPortfoli
AVERAGE(smfGetYahoo
*Ticker*
MMM $175.29 $169.58
On Wed, Jul 17, 2019 at 9:51 AM a.bereziuk@.
>
> Randy, I have tested the macro smfUpdateDownloadTa
> template: smfUpdateDownloadTa
> webpage extraction function in the template: RCHGetTableCell and
> GetElementNumber. It did not work with Yahoo functions smfGetYahooPortfoli
> and AVERAGE(smfGetYahoo
> titles.
>
>
Thu Jul 18, 2019 3:01 am (PDT) . Posted by:
a.bereziuk
The problem is that a trade price is slightly different from that returned from Yahoo. So I can quickly overwrite the returned price with the exact one and start my macro for copying and archiving the trade data. With an array entered function it is not possible to edit the cell. I only have not more than 10 positions in the portfolio. Since GetYahooQuotes() have not functioned for individual quotes, I used smfGetYahooPortfolioView(). Or is there another function (for Yahoo) for it?
Thu Jul 18, 2019 3:45 am (PDT) . Posted by:
a.bereziuk
> Having said that, the two formulas worked fine for me using smfUpdateDownloadTable:
For some reason the functions GetTableCell() and GetElementNumber() work fine with the macro. But the 2 Yahoo functions return "#value!" in the cells. They both work, when I put them directly in the cells and write "MMM" ticker directly. Could you please look, what is different in the Yahoo functions comparing to webpage extraction functions in the macro code.
The difference in my Excel is the European regional delimiters: in function calls: ";" in place of "," and in decimal numbers: "," instead of "." for US.
Which tool do you use to copy your Excel table and to paste it as a HTML-Table in the forum?
For some reason the functions GetTableCell(
The difference in my Excel is the European regional delimiters: in function calls: ";" in place of "," and in decimal numbers: "," instead of "." for US.
Which tool do you use to copy your Excel table and to paste it as a HTML-Table in the forum?
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar