Kamis, 18 Mei 2017

[smf_addin] Digest Number 4012

7 Messages

Digest #4012

Messages

Thu May 18, 2017 1:21 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

All the data is there in JSON format, as far as I can tell, including
splits and dividends.

BTW, I added two more utility functions: smfUNIX2Date() and smfDate2UNIX(),
to convert between EXCEL serial dates and UNIX dates. Since so many of the
JSON feeds, of Yahoo and others, or using the UNIX dates, they are sorely
needed these days. :(

As an example, using the new function on MSFT and asking for annual quotes
(extracted from monthly option):

Date Open High Low Close Volume Unadj Close Div Adj Split Adj Dividend Split
2017-05-18 $67.3700 $68.1300 $67.1400 $67.7100 22544585 $67.7100 1.000000
1.000000 $0.78
2017-01-01 $62.0466 $65.1296 $61.2165 $63.8846 493327400 $64.6500 0.988160
1.000000 $1.47
2016-01-01 $52.2630 $53.2924 $47.2406 $53.0038 926833100 $55.0900 0.962131
1.000000 $1.29
2015-01-01 $43.6741 $44.8441 $37.7679 $37.8147 918922500 $40.4000 0.936008
1.000000 $1.15
2014-01-01 $34.0325 $34.5245 $31.5541 $34.4790 930226200 $37.8400 0.911178
1.000000 $0.97
2013-01-01 $24.1150 $24.9823 $23.2566 $24.2920 1145054400 $27.4500 0.884956
1.000000 $0.83
2012-01-01 $22.8380 $25.7627 $22.7004 $25.4014 1354858100 $29.5300 0.860190
1.000000 $0.68
2011-01-01 $23.5011 $24.6825 $22.9733 $23.2330 1361258700 $27.7300 0.837830
1.000000 $0.55
2010-01-01 $25.1085 $25.6170 $22.6813 $23.1077 1359650900 $28.1800 0.820005
1.000000 $0.52
2009-01-01 $15.6343 $16.8110 $13.4088 $13.6890 1564043400 $17.1000 0.800525
1.000000 $0.46
2008-01-01 $28.1275 $28.2611 $24.3945 $25.6205 1950301600 $32.6000 0.785904
1.000000 $0.41
2007-01-01 $23.1892 $24.4064 $22.7938 $23.9257 1324518200 $30.8600 0.775299
1.000000 $0.37
2006-01-01 $20.0635 $21.6915 $19.9489 $21.5157 1388622700 $28.1500 0.764325
1.000000 $0.32
2005-01-01 $20.2373 $20.4639 $19.3614 $19.8447 1521414300 $26.2800 0.755124
1.000000 $3.16
2004-01-01 $18.3797 $19.2127 $18.1665 $18.4264 1232189000 $27.6500 0.666415
1.000000 $0.24 2 for 1
2003-01-01 $17.2616 $18.9185 $15.5222 $15.6642 1718662200 $47.4600 0.660100
0.500000
2002-01-01 $21.9978 $23.3081 $20.2420 $21.0275 1360115800 $63.7100 0.660100
0.500000
2001-01-01 $14.5635 $21.3707 $14.1509 $20.1537 2005531200 $61.0625 0.660100
0.500000
2000-01-01 $38.7396 $39.1522 $31.3135 $32.3036 1274875200 $97.8750 0.660100
0.500000 2 for 1
1999-01-01 $23.0390 $28.9000 $22.4434 $28.8794 1472518800 $175.0000 0.660100
0.250000 2 for 1
1998-01-01 $10.6957 $12.3872 $10.2625 $12.3098 1486108000 $149.1875 0.660100
0.125000
1997-01-01 $6.8588 $8.5194 $6.6629 $8.4163 1565781600 $102.0000 0.660100
0.125000 2 for 1
1996-01-01 $3.6254 $3.8471 $3.2953 $3.8162 2056616000 $92.5000 0.660100
0.062500
1995-01-01 $2.5373 $2.6920 $2.4032 $2.4496 1111609600 $59.3750 0.660100
0.062500 2 for 1
1994-01-01 $1.6606 $1.7946 $1.6348 $1.7560 1233651200 $85.1251 0.660100
0.031250
1993-01-01 $1.7534 $1.9390 $1.7431 $1.7843 1411331200 $86.4998 0.660100
0.031250 3 for 2
1992-01-01 $1.5230 $1.8325 $1.5059 $1.6537 1468918400 $120.2501 0.660100
0.020833 3 for 2
1991-01-01 $0.6876 $0.9122 $0.6693 $0.8996 2219728000 $98.1252 0.660100
0.013889 2 for 1
1990-01-01 $0.4000 $0.4401 $0.3851 $0.4240 2380947200 $92.4998 0.660100
0.006944
1989-01-01 $0.2452 $0.2762 $0.2303 $0.2733 1375062400 $59.6246 0.660100
0.006944
1988-01-01 $0.2510 $0.2831 $0.2407 $0.2556 1966780800 $55.7496 0.660100
0.006944 2 for 1
1987-01-01 $0.1106 $0.1776 $0.1089 $0.1676 1961596800 $73.1261 0.660100
0.003472

On Thu, May 18, 2017 at 9:59 AM, Robbie Geary rgearyiii@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
> As I can see, Randy is right that the page source code contains historical
> prices. What isn't obvious to me (without being able to decipher Unix
> timestamps in my head) is how far back the quotes go in the source code
> generated by a first hit at a symbol's page. Does the source code contain
> the complete history, or just the last 100 quotes? In other words, does it
> take a "scroll" to dynamically generate source code with more quotes, or is
> it all in there from the outset?
>
>
>

Thu May 18, 2017 1:24 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

In addition to examples in the documentation, there are several templates
on the website:

http://ogres-crypt.com/SMF/Templates/#smfPricesByDates()

On Thu, May 18, 2017 at 1:16 PM, rr76012@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> For historical download stock prices.
>
> Do you have an example spreadsheet of how to use smfPricesByDates()?
>
>
>

Thu May 18, 2017 1:47 pm (PDT) . Posted by:

tonyestep

Randy, that would be fabulous! I was sure hoping for this! For my own use, daily adjusted closes and other daily values are all I need. But others may well want weekly, monthly or quarterly data.
When you said you thought that Yahoo had made changes that shut off automated access, it set me off on a big scramble. I have some work-around schemes going, but the function you describe would be way better than any alternative I've found.
As always, thanks SO much for the great work you've done on this public service!!


Tony Estep

Thu May 18, 2017 5:13 pm (PDT) . Posted by:

eadamy

Do you have any idea yet on the historical limits for extracting data with the proposed smfGetYahooHistory()? Would I be correct in assuming that adjusted close can be calculated as: close * split adjustment * dividend adjustment?

FWIW, I have been running test downloads with Quandl's Quote Media ($50/month) and am finding that each symbol request requires 2-3 times as long as Y did to download history. I am already running across low volume ETF symbols carried by Y which are missing from QM. Two major pluses so far from the QM effort: data is available one hour after the close and I have a workable "Plan B" if Y thwarts download efforts.


Earl Adamy





Thu May 18, 2017 6:16 pm (PDT) . Posted by:

msimmsx

Sounds great. ETA on the modifications ?

Thu May 18, 2017 1:57 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry, but I don't see an export option on the Google Finance web site for
the mutual funds I tried.

On Thu, May 18, 2017 at 10:42 AM, joe_mo37@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Based on a recommendation you made last week, I'm using an array-entered
> formula to retrieve historical data from Google. This formula is working
> well for stocks and ETFs:
>
> "=smfGetCSVFile(""https://www.google.com/finance/historical?q=
> ""&$B$1&""&startdate=""&TEXT($B$2,""mmm+dd,+yyyy&quot;")&""
> &enddate=""&TEXT($B$3,""mmm+dd,+yyyy"")&""&output=csv"")"
> where B1 is ticker symbol, B2 is start date, and B3 is end date.
>
> Is there a version of this that will work for mutual funds? I know that
> the Google site returns only date and close price for mutual funds, but I
> have not been successful trying to modify the formula above to return data
> for mutual funds.
>

Thu May 18, 2017 5:51 pm (PDT) . Posted by:

joe_mo37

I noticed there was no export button on the mutual fund pages, but didn't put two and two together. Thanks for taking the time to reply Randy, and for your generous commitment of time and talent to this group!
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 19.11

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)