11 Messages
Digest #4545
Messages
Thu May 23, 2019 6:47 am (PDT) . Posted by:
wongkayau
Hi Randy,
I found another way to get yahoo data price. I use your RCHGetTableCell to download data from:
https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d
1388419200 https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d= Dec31,2013
1558540800 https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d= May23, 2019
The RCHGetTableCell formula works. However, it works only for Dec20,2018 to May23, 2019. Earlier data could not be downloaded. I guess the problem is in the url. But, in the webpage, there is no "page 2" or similar stuff in the url. How may I find the way to download all the data?
Thank you very much.
Cheers,
L
I found another way to get yahoo data price. I use your RCHGetTableCell to download data from:
https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d
1388419200 https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d= Dec31,2013
1558540800 https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d= May23, 2019
The RCHGetTableCell formula works. However, it works only for Dec20,2018 to May23, 2019. Earlier data could not be downloaded. I guess the problem is in the url. But, in the webpage, there is no "page 2" or similar stuff in the url. How may I find the way to download all the data?
Thank you very much.
Cheers,
L
Thu May 23, 2019 8:29 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
There is no page 2 because it's an "infinite scroll" process without a
unique URL. So you can only pull what you can see from using the initial
URL.
On Thu, May 23, 2019 at 6:52 AM wongkayau@... wrote:
>
> I found another way to get yahoo data price. I use your RCHGetTableCell to
> download data from:
>
> https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d
>
> 1388419200
> <https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d>=
> Dec31,2013
> 1558540800
> <https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d>=
> May23, 2019
>
> The RCHGetTableCell formula works. However, it works only for Dec20,2018
> to May23, 2019. Earlier data could not be downloaded. I guess the problem
> is in the url. But, in the webpage, there is no "page 2" or similar stuff
> in the url. How may I find the way to download all the data?
>
>
unique URL. So you can only pull what you can see from using the initial
URL.
On Thu, May 23, 2019 at 6:52 AM wongkayau@... wrote:
>
> I found another way to get yahoo data price. I use your RCHGetTableCell to
> download data from:
>
> https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d
>
> 1388419200
> <https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d>=
> Dec31,2013
> 1558540800
> <https://finance.yahoo.com/quote/0220.HK/history?period1=1388419200&period2=1558540800&interval=1d&filter=history&frequency=1d>=
> May23, 2019
>
> The RCHGetTableCell formula works. However, it works only for Dec20,2018
> to May23, 2019. Earlier data could not be downloaded. I guess the problem
> is in the url. But, in the webpage, there is no "page 2" or similar stuff
> in the url. How may I find the way to download all the data?
>
>
Thu May 23, 2019 8:34 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You need to use EXCEL's TRANSPOSE() function. However, since the function
defaults to returning an array the size of the range you array-enter it
over, you also need to override that and tell the function how many rows
and columns to use before transposing the data. For example:
=TRANSPOSE(smfGetYahooHistory("MMM",,,,"c",0,,20000,12))
On Thu, May 23, 2019 at 4:25 AM wongkayau@... wrote:
>
> A quick question: how to invert the output array table from
> smfGetYahooHistory? Now, heading is at the top of the data series. Is it
> possible to make the headings at the left hand side?
>
> I found a similar question asked, but i could not find the solution.
> https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/22220
>
>
>
defaults to returning an array the size of the range you array-enter it
over, you also need to override that and tell the function how many rows
and columns to use before transposing the data. For example:
=TRANSPOSE(smfGetYahooHistory("MMM",,,,"c",0,,20000,12))
On Thu, May 23, 2019 at 4:25 AM wongkayau@... wrote:
>
> A quick question: how to invert the output array table from
> smfGetYahooHistory? Now, heading is at the top of the data series. Is it
> possible to make the headings at the left hand side?
>
> I found a similar question asked, but i could not find the solution.
> https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/22220
>
>
>
Thu May 23, 2019 10:01 am (PDT) . Posted by:
wongkayau
thanks for your quick reply.
too bad! then the max download each time is 100 date. the solution is so i divide download into 14 times for each stock.
i played around the transpose formula just given by you, and then understand row number is the length of the data i want. however, i don't understand col number: 1 is date, then 2-12 is closing price. isn't it?
many thanks
too bad! then the max download each time is 100 date. the solution is so i divide download into 14 times for each stock.
i played around the transpose formula just given by you, and then understand row number is the length of the data i want. however, i don't understand col number: 1 is date, then 2-12 is closing price. isn't it?
many thanks
Thu May 23, 2019 10:52 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The rows would be based on the number of dates you are requesting and the
columns would be based on which fields you are requesting.
In my example, I was only requesting one column of data from the function,
but gave the function instructions to make room for up to 12 columns. I
just threw the 20000 by 12 in because it should be room enough for whatever
subset is requested. It's for the array of data that is being created
before it gets transposed.
On Thu, May 23, 2019 at 10:02 AM wongkayau@... wrote:
>
> i played around the transpose formula just given by you, and then
> understand row number is the length of the data i want. however, i don't
> understand col number: 1 is date, then 2-12 is closing price. isn't it?
>
>
>
columns would be based on which fields you are requesting.
In my example, I was only requesting one column of data from the function,
but gave the function instructions to make room for up to 12 columns. I
just threw the 20000 by 12 in because it should be room enough for whatever
subset is requested. It's for the array of data that is being created
before it gets transposed.
On Thu, May 23, 2019 at 10:02 AM wongkayau@..
>
> i played around the transpose formula just given by you, and then
> understand row number is the length of the data i want. however, i don't
> understand col number: 1 is date, then 2-12 is closing price. isn't it?
>
>
>
Thu May 23, 2019 7:00 am (PDT) . Posted by:
pietertvl
Hi Randy ... could be a temporary glitch, but Yahoo Portfolio View fetch isn't updating for me today. Quotes stuck at close for yesterday. Can you confirm? Anyone else having similar issues? TIA.
Thu May 23, 2019 7:05 am (PDT) . Posted by:
midhenry1
I am seeing the same issue. Quotes stuck at closing 5/22
Thu May 23, 2019 7:48 am (PDT) . Posted by:
midhenry1
Yahoo is behaving badly. They are showing yesterday's closing for all US indices as of 7:48am PST
Thu May 23, 2019 8:12 am (PDT) . Posted by:
"Pieter van Leeuwen" pietertvl
They are back up as of 11:10 EST.
But Randy, I'd welcome any ideas for a serviceable back up option that delivers spot quotes OHLC intraday only, for small quote lists.
Can you point me/us to anything you already have going along those lines?
Thanks!
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Thursday, May 23, 2019 10:48 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Get Yahoo Portfolio View
Yahoo is behaving badly. They are showing yesterday's closing for all US indices as of 7:48am PST
But Randy, I'd welcome any ideas for a serviceable back up option that delivers spot quotes OHLC intraday only, for small quote lists.
Can you point me/us to anything you already have going along those lines?
Thanks!
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Thursday, May 23, 2019 10:48 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Get Yahoo Portfolio View
Yahoo is behaving badly. They are showing yesterday'
Thu May 23, 2019 8:46 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Glad to hear it's working again.
Depending on what ticker symbols you are using, the easiest replacement
would be Stooq, which creates a CSV very similar to the one Yahoo used to
have. So:
=smfGetCSVFile("https://stooq.com/q/l/?s=mmm.us+ibm.us&f=sd2t2ohlcm2vr&e=csv
")
....returns something like:
MMM.US <http://mmm.us/> 2017-07-07 22:00:00 $208.50 $210.15 $208.05 $209.59
0.75% 1477830 259895497
IBM.US <http://ibm.us/> 2017-07-07 22:00:00 $152.62 $153.49 $152.14 $152.94
0.38% 2383896 304347109
....where the URL parameters are:
-- &s= is for the ticker symbol, and US ticker symbols must have the ".us"
suffix. Multiple ticker symbols can be used with a "+" in between them.
-- &f= is for the data fields, with meanings of:
>> a = Ask
>> b = Bid
>> c = Close
>> d1 = Date as yyyymmdd
>> d2 = Date as m/d/yyyy
>> d4 = Date as d-mmm-yy
>> h = High
>> l = Low
>> m1 = $ Change
>> m2 = % Change
>> n = Name
>> o = Open
>> p = Previous Close
>> r = Rotation???
>> s = Symbol
>> t1 = Time as hhmmss (24 hour)
>> t2 = Time as hh:mm:ss (24 hour)
>> t3 = Time as hh:mm (24 hour)
>> t4 = Time as hh:mmPM
>> v = Volume
On Thu, May 23, 2019 at 8:12 AM 'Pieter van Leeuwen' pieter_biz@... wrote:
>
> They are back up as of 11:10 EST.
>
> But Randy, I'd welcome any ideas for a serviceable back up option that
> delivers spot quotes OHLC intraday only, for small quote lists.
>
> Can you point me/us to anything you already have going along those lines?
>
>
>
>
Depending on what ticker symbols you are using, the easiest replacement
would be Stooq, which creates a CSV very similar to the one Yahoo used to
have. So:
=smfGetCSVFile("https://stooq.com/q/l/?s=mmm.us+ibm.us&f=sd2t2ohlcm2vr&e=csv
")
....returns something like:
MMM.US <http://mmm.us/> 2017-07-07 22:00:00 $208.50 $210.15 $208.05 $209.59
0.75% 1477830 259895497
IBM.US <http://ibm.us/> 2017-07-07 22:00:00 $152.62 $153.49 $152.14 $152.94
0.38% 2383896 304347109
....where the URL parameters are:
-- &s= is for the ticker symbol, and US ticker symbols must have the ".us"
suffix. Multiple ticker symbols can be used with a "+" in between them.
-- &f= is for the data fields, with meanings of:
>> a = Ask
>> b = Bid
>> c = Close
>> d1 = Date as yyyymmdd
>> d2 = Date as m/d/yyyy
>> d4 = Date as d-mmm-yy
>> h = High
>> l = Low
>> m1 = $ Change
>> m2 = % Change
>> n = Name
>> o = Open
>> p = Previous Close
>> r = Rotation???
>> s = Symbol
>> t1 = Time as hhmmss (24 hour)
>> t2 = Time as hh:mm:ss (24 hour)
>> t3 = Time as hh:mm (24 hour)
>> t4 = Time as hh:mmPM
>> v = Volume
On Thu, May 23, 2019 at 8:12 AM 'Pieter van Leeuwen' pieter_biz@.
>
> They are back up as of 11:10 EST.
>
> But Randy, I'd welcome any ideas for a serviceable back up option that
> delivers spot quotes OHLC intraday only, for small quote lists.
>
> Can you point me/us to anything you already have going along those lines?
>
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar