Data

Is it possible to obtain data on stock trades in Excel? Ideally format would be date and time, stock name, volume and price. I can get this data for my own trades through History, but I can't get it for all trades in the stocks I'm following.

Hi Predictable,

Have you had a play with the API? Here's an example of the history available for stocks through the API:

[www.ipredict.co.nz]

Quote
bkd
Fiddling around with the URL gives you different stocks (just replace GST.UP.JAN11 with some other stock name), or try changing the "action" attribute to get different data like "history", "prices" etc.

Extensive API documentation

I am working on an easy-to-use web interface for the API information, I would really appreciate it if you could send me a quick email ( [email protected] ) so I can get an idea of what information you're looking for.

Hi Predictable, we can provide one-off data for research purposes free of charge. If you want to get regular data for trading suggest you go with Pipe's suggestions here.

Thanks admin

I've sent pipe an email and am waiting to hear back

Hey Predictable,

I replied to your email a few days ago, maybe it ended up in a spam folder?

Edit: Resent the email.



Edited 1 time(s). Last edit at 02/18/2010 04:39PM by pipe42.

Hi, I'm also trying to import into excel.

However the book action always seems to fail, while the history action does connect properly.

When setting up the web query it gives an error message about mixed security for both; I'm not sure if that's the problem.

Hi 3peter, is that the standard API you are having trouble with?

Have you tried my API web interface? It is located here.

Select the stock you are interested in using the dropdown, then click the alarm clock (represents 'history'). This brings in stock history for the last 24 hours.

The history for stock trades can be displayed for up to a year and you can copy-and-paste the displayed table directly into Excel or OpenOffice.

Hopefully this helps!

Yea, I had a look at your website but I would prefer not to cut and paste. I'm interested in bringing back the order book of a stock; I've been trying to bring back this url Book in excel, so i can just click "refresh all" and have several stocks update at once.

I was able to get the History url to bring back the data and refresh properly.

The error message says unable to open "URL" cannot download the information you requested. - this message is often associated with web content that has mixed security, but because the history one works I don't think this is the problem.

[www.erlandsendata.no]

[office.microsoft.com]

If I understand these aticles, your have two options: lower your security settings in internet explorer (bad) or sign your macro (good).

I've had a look at those sites before but they don't seem to be really related to this problem - importing from web isn't a macro it's a query.

The security side seems more likely this web site seems to be the closest to the issue.

when opening the url [www.ipredict.co.nz] it works as expected but [www.ipredict.co.nz] produces the error "Unable to connect to the web server"

that suggests it's a server problem but I don't understand why the same server would allow the first query but not the second.

Wow, that Microsoft link is just extraordinarily unhelpful isn't it!

Quote
Microsoft
Our product won't do what you want, we know all about it, and no, we aren't going to help you fix it.

Can you import from this URL?

/796551b68e07040fc8e7c28369526a3e/ajax/3peter.php?stock_name=GST.UP.JAN11

Yes, your link works perfectly.

Great. It should work in the same way as the API URLs - just put the stock name in as required.

Great stuff, Pipe

Hi Guys,

Any idea why it is that when we try to get the data direct from the IPredict API it fails, but it works fine from Pipe's website?

I am intrigued as to what is causing the problem.

Also, is there any way to get all the price data for all open contracts in one go from your site Pipe?

Thanks,

Alan.

Hey Alan,

I think the API URLs have changed, although there seems to be some kind of redirect from the old URLs:

/api.php?action=history&stock=FITCH.NZEX

... to the new URLs:

/app.php?do=api&action=history&stock=FITCH.NZEX

Which functions have stopped working for you?

I have noticed some of the minor API functions have stopped working or work differently, for example, action=tradeHist has stopped working (gah) and action=stock&results=extended is no longer giving out detailed information for closed stocks .

Anyway Alan, do you want just the current price data for all contracts, or the order book data for all contracts? I can do all the current prices quite easily, but I think the second one will take too long to load.

Hi Pipe42,

>
> I think the API URLs have changed, although there
> seems to be some kind of redirect from the old
> URLs:
>
> [www.ipredict.co.nz];
> stock=FITCH.NZEX
>
> ... to the new URLs:
>
> [www.ipredict.co.nz]
> istory&stock=FITCH.NZEX
>
> Which functions have stopped working for you?
>

I can't get anything (I have tried) to work directly from the IPredict server. For example, I just tried this one:

[www.ipredict.co.nz]

It errors out though.

Do you want to know the exact error message? It is the same in Excel 2000 and Excel 2007.

>
> I have noticed some of the minor API functions
> have stopped working or work differently, for
> example, action=tradeHist has stopped working
> (gah) and action=stock&results=extended is no
> longer giving out detailed information for closed
> stocks .
>
> Anyway Alan, do you want just the current price
> data for all contracts, or the order book data for
> all contracts? I can do all the current prices
> quite easily, but I think the second one will take
> too long to load.

The current price data (current highest buy and lowest sell) would be great (essentially what you get on the 'browse all' page).

The full order book for all contracts would be awesome - I agree it would be slow, but awesome nevertheless.

Alan.

PS: Just want to say 'Thanks!' for your efforts - fantastic work!

PPS: How do you get the URLs to show properly rather than as [www.ipredict.co.nz] ?

Cheers Alan.

Brand-spank'in new All Stock Prices page added just for you.

/796551b68e07040fc8e7c28369526a3e/all_prices.php

With Excel, it sounds like you are having the same error as Predictable was having above. I might be able to figure out a workaround for you, give me a moment.

PS: I get the URLs to show properly by using the Insert Link button (the world with a paperclip icon) in the formatting bar:

• Type/paste in the url
• Select the url
• Click the link button
• Paste in your url

... now the URL links and shows correctly.

pipe42 Wrote:
-------------------------------------------------------
> Cheers Alan.
>
> Brand-spank'in new All Stock Prices page added
> just for you.
>
> [api.howison.co.nz]
>
> With Excel, it sounds like you are having the same
> error as Predictable was having above. I might be
> able to figure out a workaround for you, give me a
> moment.
>
> PS: I get the URLs to show properly by using the
> Insert Link button (the world with a paperclip
> icon) in the formatting bar:
>
>
> [*]Type/paste in the url
> [*]Select the url
> [*]Click the link button
> [*]Paste in your url
>
>
> ... now the URL links and shows correctly.


Pipe - I am honoured!

Thank you.

I just re-read above, and you are correct - I think it is the same issue as above.

Could it be due to the secure / insecure issue? There is no 'code' (VBA) involved, so there is nothing to 'trust / sign' as was suggested above.

Any ideas you have, I am happy to investigate. It is more just out of stubborn interest at this point, since you have solved it anyway, but I would like to know why Excel isn't getting it.

Thanks again,

Alan.

I have made a simple API passthrough you can try. It takes exactly the same arguments as the ordinary API, check it out:

iPredict API URL:

/app.php?do=api&action=history&stock=MORT.2010.700

Passthrough URL:

/796551b68e07040fc8e7c28369526a3e/api_passthrough.php?do=api&action=history&stock=MORT.2010.700

Let me know if this works.

pipe42 Wrote:
-------------------------------------------------------
> I have made a simple API passthrough you can try.
> It takes exactly the same arguments as the
> ordinary API, check it out:
>
> iPredict API URL:
>
> [www.ipredict.co.nz]
> istory&stock=MORT.2010.700
>
> Passthrough URL:
>
> [api.howison.co.nz]
> i&action=history&stock=MORT.2010.700
>
> Let me know if this works.


The pass-through URL works perfectly.

Perhaps that means it is the TLS connection that is causing the issue?

Alan.

Yeah, I think Excel probably can't read the security certificate or something like that.

Anyway, thanks for the idea for the All Stock prices page - it looks great.

pipe42 Wrote:
-------------------------------------------------------
> Yeah, I think Excel probably can't read the
> security certificate or something like that.
>
> Anyway, thanks for the idea for the All Stock
> prices page - it looks great.


I'll see what I can find, but I'm pretty sure Excel can do TLS data imports - perhaps I just need to find more info.

Either way, thanks for the extra data feed.

If you do put the 'all prediction' full board info one together, please do post back here and I'll test it out.

Thanks,

Alan.

Hi Pipe42,

I have been checking your 'all contracts' report, and it appears that something is not quite working right yet.

I just pulled it, and it shows no sell price for 91.FEB10.VHIGH.

If I go to the IPredict web server and browse the 'all contracts' page it shows a lowest sell of 0.0005.


I wonder if it is a rounding issue below a certain threshold?

If I can narrow it down further, I'll post back, but I figured this might be enough for you to go on.


Thanks,

Alan.

Ah, of course - thanks for pointing that out.

The issue occurs because very small numbers like 0.0004 are reported like this:

1.0E-4

I previously figured out how to handle this on the History page, I have coped the fix to the all stocks page now.

pipe42 Wrote:
-------------------------------------------------------
> Ah, of course - thanks for pointing that out.
>
> The issue occurs because very small numbers like
> 0.0004 are reported like this:
>
> 1.0E-4
>
> I previously figured out how to handle this on the
> History page, I have coped the fix to the all
> stocks page now.


Yep - that fixed it (both on your web server and also when importing into Excel).

Thanks!

Alan.

Great work, Pipe.

Sorry about the API issues, we're looking at it today.

Hi,
The api data issue should be fixed now.

Please let us know if you still have issues there.

btw, we are going to be in a position to do more work on the API pretty soon. I'm going to revive the ipredict google group [groups.google.com] and will be keen to hear about the sorts of things you'd like to be able to get from the API.

Hi Admin,

Do you have any insights on the difficulty with getting the data directly into Excel as was discussed above?

Pipe's site is great, but I am intrigued as to what is going wrong when we try to pull it in directly.

Thanks,

Alan.

Hi Pipe,

I think something has gone wrong with your 'All Stocks 'API webpage this morning.

Up until yesterday, it was producing a nice clean data set.

Now it has loads of white space coming through when imported into Excel.

It isn't obvious from the webpage itself (at least not to me!), but definitely a problem when imported.

If I can help narrow it down, by all means post back with instructions or questions, and I'll do my best.

I know for sure I haven't changed anything in excel, so I am 99% certain is must be further 'up' the chain, but I won't rule any possibilities out....

Alan.