insert XML URL

[rivet]

I am trying to get some XML data (currency exchange ) inserted into a field, but not luck.

 

URL

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.xchange%20where%20pair%20in%20(%22USDCAD%22)&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys

[comment]

Please elaborate on the process (you posted this in Calculations!) and on "no luck".

 

Also, why don't you import the response, instead of sticking it into a field?

[rivet]

attached is my test file.  Test on left, desired on right.  

 

I figured I could push the URL in to a field to be parse by the custom function.

Import sounds like a good idea, but to be honest I have never tackled xsl.

 

 

insertXMLurl.fmp12.zip

[comment]

I'm afraid I am stuck with version 11 ATM, so can't look at your file.

 

 

Import sounds like a good idea, but to be honest I have never tackled xsl.

 

If you can post a link to the API specs, I'll help you with the XSLT.

[rivet]

correction 

http://developer.yahoo.com/yql/console/?q=show%20tables&env=store://datatables.org/alltableswithkeys#h=select+*+from+yahoo.finance.xchange+where+pair+in+(%22CADUSD%22)

[comment]

Try the attached:

 

Compressed file.zip

 

Note:

The date needs more work. Their date is m/d/y and in my file it is routed to a Text field.

[rivet]

Hey Comment, this is great, I appreciate the help. Thank you.

 

Q:  What details did the API url give you to help with this?

[comment]

Q:  What details did the API url give you to help with this?

 

None, really. I was trying to understand if there was a way to get some control over the response through the URL. It seems that this is an undocumented service; I am not sure how you stumbled upon it.

 

 

Update:

The description can be found here:

http://developer.yahoo.com/yql/console/?q=show%20tables&env=store://datatables.org/alltableswithkeys#h=desc+yahoo.finance.xchange

 

But alas there is no more info there to be found.

Edited January 29, 2014 by comment

[rivet]

FYI: when converted to fmp12, the import mapping dialogue will show the left side but zero records to import. 

 

UPDATE

Client12 - works

Client13 - does not work

[comment]

Can you still import manually?

[rivet]

I took the xml results and built a static source file which worked.
I am now looking to see if there are any fmp13 release note about this.

[rivet]

my php guy came up with this:

<?php

// Get cURL resource
$curl = curl_init();

//Define header array for cURL requestes
$header = array('Contect-Type:application/xml', 'Accept:application/xml');


// Set some options - we are passing in a useragent too here
curl_setopt_array($curl, array(
	CURLOPT_HTTPHEADER => $header, //Set http header options
    CURLOPT_URL => 'http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.xchange%20where%20pair%20in%20(%22CADUSD%22)&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys',
    CURLOPT_HTTPGET => TRUE //Set cURL to GET method
));
// Send the request & save response to $resp
$resp = curl_exec($curl);

// Close request to clear up some resources
curl_close($curl);
exit;
?>

this is an extra layer, but for now it should do, while I investigate FMP or perhaps I will investigate another source for exchanges rates

 

[rivet]

wow, complete bust on the XML. Had to write script loop incase import resulted in a null, then when I set a schedule on server13 (win) it was giving me 718 errors.

Plan B!

URL: http://rate-exchange.appspot.com/currency?from=USD&to=CAD&q=1

result: {"to": "CAD", "rate": 1.11666, "from": "USD", "v": 1.11666}

inserted url into a field then parsed.

Just one of those days.

(Hey Comment, as always, thanks for your time and help)

[comment]

I don't know: it seems very strange to me. There has been a change in v.13 with regard to requesting a XML response, but I don't think it should break existing requests. Have you tried reconstructing the import step from scratch in v.13? If it doesn't work, you should report it as a bug.