9 Messages
Digest #3323
Messages
Sat Feb 14, 2015 2:06 am (PST) . Posted by:
"Manish Sahajwani" manish_sahajwani
Hi Randy,
I think I referred to the same thing that you suggested. So, if I build a
URL like the following, it will return the EUR/USD rates:
http://www.oanda.com/currency/historical-rates/download?quote_currency=EUR&end_date=2014-8-10&start_date=2014-6-1&period=daily&display=absolute&rate=0&date_range=c&price=bid&view=table&base_currency_0=USD&download=csv
Thanks for help.
Regards,
Manish
On Fri, Feb 13, 2015 at 6:46 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> What API are you referring to?
>
> On Thu, Feb 12, 2015 at 8:52 AM, Manish Sahajwani msahajwani@...wrote:
>
>>
>> I was just wondering if it is not possible to create a few more functions
>> that allow you to directly read the api for fx calls rather than relying on
>> the html table?
>>
>>
>
>
I think I referred to the same thing that you suggested. So, if I build a
URL like the following, it will return the EUR/USD rates:
http://www.oanda.com/currency/historical-rates/download?quote_currency=EUR&end_date=2014-8-10&start_date=2014-6-1&period=daily&display=absolute&rate=0&date_range=c&price=bid&view=table&base_currency_0=USD&download=csv
Thanks for help.
Regards,
Manish
On Fri, Feb 13, 2015 at 6:46 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> What API are you referring to?
>
> On Thu, Feb 12, 2015 at 8:52 AM, Manish Sahajwani msahajwani@.
>
>>
>> I was just wondering if it is not possible to create a few more functions
>> that allow you to directly read the api for fx calls rather than relying on
>> the html table?
>>
>>
>
>
Sat Feb 14, 2015 2:31 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Ahhh. OK. So you could grab the data using the add-in with something like:
=smfGetCSVFile("
http://www.oanda.com/currency/historical-rates/download?quote_currency=EUR&end_date=2014-8-10&start_date=2014-6-1&period=daily&display=absolute&rate=0&date_range=c&price=bid&view=table&base_currency_0=USD&download=csv
")
...and in the case of wanting an exchange rate for a given date, you could
even do something like:
=INDEX(smfGetCSVFile("
http://www.oanda.com/currency/historical-rates/download?quote_currency=CAD&end_date=2015-2-7&period=daily&display=absolute&rate=0&date_range=c&price=bid&view=table&base_currency_0=USD&download=csv
",,10,2),6,2)
On Sat, Feb 14, 2015 at 3:06 AM, Manish Sahajwani msahajwani@... wrote:
>
> I think I referred to the same thing that you suggested. So, if I build a
> URL like the following, it will return the EUR/USD rates:
>
>
> http://www.oanda.com/currency/historical-rates/download?quote_currency=EUR&end_date=2014-8-10&start_date=2014-6-1&period=daily&display=absolute&rate=0&date_range=c&price=bid&view=table&base_currency_0=USD&download=csv
>
>
>
=smfGetCSVFile("
http://www.oanda.com/currency/historical-rates/download?quote_currency=EUR&end_date=2014-8-10&start_date=2014-6-1&period=daily&display=absolute&rate=0&date_range=c&price=bid&view=table&base_currency_0=USD&download=csv
")
...and in the case of wanting an exchange rate for a given date, you could
even do something like:
=INDEX(smfGetCSVFile("
http://www.oanda.com/currency/historical-rates/download?quote_currency=CAD&end_date=2015-2-7&period=daily&display=absolute&rate=0&date_range=c&price=bid&view=table&base_currency_0=USD&download=csv
",,10,2),6,2)
On Sat, Feb 14, 2015 at 3:06 AM, Manish Sahajwani msahajwani@... wrote:
>
> I think I referred to the same thing that you suggested. So, if I build a
> URL like the following, it will return the EUR/USD rates:
>
>
> http://www.oanda.com/currency/historical-rates/download?quote_currency=EUR&end_date=2014-8-10&start_date=2014-6-1&period=daily&display=absolute&rate=0&date_range=c&price=bid&view=table&base_currency_0=USD&download=csv
>
>
>
Fri Feb 13, 2015 7:09 am (PST) . Posted by:
rjemery7
Randy,
For ^SP600, historical prices are not available on Yahoo Finance but are available on Google Finance (under SP600). What SMF construct(s) could I use to retrieve closing prices for specific dates from Google? A working example would be appreciated.
For ^SP600, historical prices are not available on Yahoo Finance but are available on Google Finance (under SP600). What SMF construct(s) could I use to retrieve closing prices for specific dates from Google? A working example would be appreciated.
Fri Feb 13, 2015 8:32 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Unfortunately, Google doesn't offer a CSV file for the SP600 ticker symbol.
And they don't fully code HTML table tags, for the RCHGetHTMLTable() or
RCHGetTableCell() functions to work. So the only option is to grab the data
one cell at a time with smfGetTagContent().
Take a look at the smfGetTagContent-DJIA-Quotes-From-Google.xls template in
the FILES area of the Yahoo group. You just need to change the ticker
symbol in cell B4 of the workbook.
On Fri, Feb 13, 2015 at 8:09 AM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> For ^SP600, historical prices are not available on Yahoo Finance but are
> available on Google Finance (under SP600). What SMF construct(s) could I
> use to retrieve closing prices for specific dates from Google? A working
> example would be appreciated.
>
And they don't fully code HTML table tags, for the RCHGetHTMLTable() or
RCHGetTableCell() functions to work. So the only option is to grab the data
one cell at a time with smfGetTagContent().
Take a look at the smfGetTagContent-DJIA-Quotes-From-Google.xls template in
the FILES area of the Yahoo group. You just need to change the ticker
symbol in cell B4 of the workbook.
On Fri, Feb 13, 2015 at 8:09 AM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> For ^SP600, historical prices are not available on Yahoo Finance but are
> available on Google Finance (under SP600). What SMF construct(s) could I
> use to retrieve closing prices for specific dates from Google? A working
> example would be appreciated.
>
Fri Feb 13, 2015 12:47 pm (PST) . Posted by:
rjemery7
Randy,
In the referenced template, in B4, exactly what in "INDEXDJX%3A.DJI" gets changed to specify "SP600"? Please be explicit.
In the referenced template, in B4, exactly what in "INDEXDJX%
Fri Feb 13, 2015 3:43 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Just as it is on Google:
INDEXSP:SP600
On Fri, Feb 13, 2015 at 1:47 PM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> In the referenced template, in B4, exactly what in "INDEXDJX%3A.DJI" gets
> changed to specify "SP600"? Please be explicit.
>
INDEXSP:SP600
On Fri, Feb 13, 2015 at 1:47 PM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> In the referenced template, in B4, exactly what in "INDEXDJX%
> changed to specify "SP600"
>
Sat Feb 14, 2015 1:09 am (PST) . Posted by:
rjemery7
Randy,
I do not understand your template file.
First, for Tags, what are all the various type codes, what do they mean and how are they applied? Examples of situations for each type code would be beneficial if included on your documentation page.
Do you have "table, td, li, span, etc" documented elsewhere?
If all I wish to do is obtain the closing price on a certain date, say 12/31/14. what would the smfGetTagContent function call look like?
=smfGetTagContent("url", ?????)
where url would presumably be http://www.google.com/finance/historical?cid=6274520&startdate=Dec+31%2C+2014&enddate=Dec+31%2C+2014&num=30&ei=BwnfVKGMMJCLsgeJjYFo
Ultimately, for the SP600, I seek the closing price on the last trading day of the year for the past six years.
I do not understand your template file.
First, for Tags, what are all the various type codes, what do they mean and how are they applied? Examples of situations for each type code would be beneficial if included on your documentation page.
Do you have "table, td, li, span, etc" documented elsewhere?
If all I wish to do is obtain the closing price on a certain date, say 12/31/14. what would the smfGetTagContent function call look like?
=smfGetTagContent("url", ?????)
where url would presumably be http://www.google.com/finance/historical?cid=6274520&startdate=Dec+31%2C+2014&enddate=Dec+31%2C+2014&num=30&ei=BwnfVKGMMJCLsgeJjYFo
Ultimately, for the SP600, I seek the closing price on the last trading day of the year for the past six years.
Sat Feb 14, 2015 2:22 am (PST) . Posted by:
"Randy Harmelink" rharmelink
The "tags" are regular HTML tags, of the type that have corresponding
opening and closing tags, such as "<td" and "</td". for a list of HTML
tags, see:
http://www.devguru.com/technologies/html/6149
In the case of the smfGetTagContent() function, I have code that identifies
where a "</td" tag SHOULD be coded, even if the web page doesn't code it.
That's why smfGetTagContent() works on the Google web page, but
RCHGetTableCell() does not. And RCHGetHTMLTable() fails because they don't
code the "</tr" tags to end a table row.
For example, if you look at the web page in question, you'll see table rows
that look like:
<tr>
<td class="lm">Jan 13, 2015
<td class="rgt">680.16
<td class="rgt">690.60
<td class="rgt">673.32
<td class="rgt">680.39
<td class="rgt rm">89,718,541
<tr>
"Correctly" coded, that would have the ending tags for the row and the
cells, as:
<tr>
<td class="lm">Jan 13, 2015*</td>*
<td class="rgt">680.16*</td>*
<td class="rgt">690.60*</td>*
<td class="rgt">673.32*</td>*
<td class="rgt">680.39*</td>*
<td class="rgt rm">89,718,541*</td>*
*</tr>*
<tr>
As you noted, you'll need to do a separate URL for each year-end. For
example, I would use:
=smfConvertData(smfGetTagContent("
https://www.google.com/finance/historical?q=INDEXSP:SP600&startdate=Dec+25%2C+2014&enddate=Dec+31%2C+2014
","td",5,">Volume"))
Note that the starting date has to be an earlier date, just in case the
30th and 31st of the month are a weekend. This makes sure that the last
trading date of the year will be in the first row of the table on the web
page, even if it is unknown ahead of time.
By the way, note that you can't use those prices to generate total return
values for each year. You'd need to add the dividend effects to get total
return.
On Sat, Feb 14, 2015 at 2:09 AM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I do not understand your template file.
>
> First, for Tags, what are all the various type codes, what do they mean
> and how are they applied? Examples of situations for each type code would
> be beneficial if included on your documentation page.
>
> Do you have "table, td, li, span, etc" documented elsewhere?
>
> If all I wish to do is obtain the closing price on a certain date, say
> 12/31/14. what would the smfGetTagContent function call look like?
>
> =smfGetTagContent("url", ?????)
>
> where url would presumably be
> http://www.google.com/finance/historical?cid=6274520&startdate=Dec+31%2C+2014&enddate=Dec+31%2C+2014&num=30&ei=BwnfVKGMMJCLsgeJjYFo
>
> Ultimately, for the SP600, I seek the closing price on the last trading
> day of the year for the past six years.
>
opening and closing tags, such as "<td" and "</td". for a list of HTML
tags, see:
http://www.devguru.com/technologies/html/6149
In the case of the smfGetTagContent() function, I have code that identifies
where a "</td" tag SHOULD be coded, even if the web page doesn't code it.
That's why smfGetTagContent() works on the Google web page, but
RCHGetTableCell() does not. And RCHGetHTMLTable() fails because they don't
code the "</tr" tags to end a table row.
For example, if you look at the web page in question, you'll see table rows
that look like:
<tr>
<td class="lm">Jan 13, 2015
<td class="rgt">680.16
<td class="rgt">690.60
<td class="rgt">673.32
<td class="rgt">680.39
<td class="rgt rm">89,718,541
<tr>
"Correctly" coded, that would have the ending tags for the row and the
cells, as:
<tr>
<td class="lm">Jan 13, 2015*</td>*
<td class="rgt">680.16*</td>*
<td class="rgt">690.60*</td>*
<td class="rgt">673.32*</td>*
<td class="rgt">680.39*</td>*
<td class="rgt rm">89,718,541*</td>*
*</tr>*
<tr>
As you noted, you'll need to do a separate URL for each year-end. For
example, I would use:
=smfConvertData(smfGetTagContent("
https://www.google.com/finance/historical?q=INDEXSP:SP600&startdate=Dec+25%2C+2014&enddate=Dec+31%2C+2014
","td",5,">Volume"))
Note that the starting date has to be an earlier date, just in case the
30th and 31st of the month are a weekend. This makes sure that the last
trading date of the year will be in the first row of the table on the web
page, even if it is unknown ahead of time.
By the way, note that you can't use those prices to generate total return
values for each year. You'd need to add the dividend effects to get total
return.
On Sat, Feb 14, 2015 at 2:09 AM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I do not understand your template file.
>
> First, for Tags, what are all the various type codes, what do they mean
> and how are they applied? Examples of situations for each type code would
> be beneficial if included on your documentation page.
>
> Do you have "table, td, li, span, etc" documented elsewhere?
>
> If all I wish to do is obtain the closing price on a certain date, say
> 12/31/14. what would the smfGetTagContent function call look like?
>
> =smfGetTagContent("url", ?????)
>
> where url would presumably be
> http://www.google.com/finance/historical?cid=6274520&startdate=Dec+31%2C+2014&enddate=Dec+31%2C+2014&num=30&ei=BwnfVKGMMJCLsgeJjYFo
>
> Ultimately, for the SP600, I seek the closing price on the last trading
> day of the year for the past six years.
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar