push yahoo finance json data into database

[Oscar Buijten]

Hi there,

The python learning curve seems to be steeper than expected :-(

As yesterday, help converting my php app into python/django will be appreciated.

I have the following:

--------------------  snip  -------------------

from django.core.management.base import BaseCommand, CommandError

import json

from collections import OrderedDict

from yahoo_finance import Share

from data.models import MyichiTickers, MyichiHistoricalData

exchange  = 'AMS'

startdate = '2014-04-25'

enddate   = '2014-04-29'

minimum_avg_daily_volume = '100000'

class Command(BaseCommand):

def handle(self, *args, **options):

from yahoo_finance import Share

from data.models import MyichiTickers

tickers_for_exchange = MyichiTickers.objects.filter(exchange=exchange)[:2]

if tickers_for_exchange:

for tickerlist in tickers_for_exchange:

ticker = Share(tickerlist.ticker)

if ticker.get_avg_daily_volume() > minimum_avg_daily_volume:

data = json.dumps(ticker.get_historical(startdate, enddate))

data = json.loads(data)

print data

--------------------  snip  -------------------

It may not be perfect, but it works.

I would need to get 'data' into the 'MyichiHistoricalData' model

The output is like this;

--------------------  snip  -------------------

[{u'High': u'6.565', u'Symbol': u'AGN.AS', u'Adj_Close': u'6.31564', u'Volume': u'4376000', u'Low': u'6.428', u'Date': u'2014-04-29', u'Close': u'6.551', u'Open': u'6.435'}, {u'High': u'6.479', u'Symbol': u'AGN.AS', u'Adj_Close': u'6.18742', u'Volume': u'3163200', u'Low': u'6.38', u'Date': u'2014-04-28', u'Close': u'6.418', u'Open': u'6.428'}, {u'High': u'6.535', u'Symbol': u'AGN.AS', u'Adj_Close': u'6.18259', u'Volume': u'4780100', u'Low': u'6.379', u'Date': u'2014-04-25', u'Close': u'6.413', u'Open': u'6.529'}]

[{u'High': u'13.94', u'Symbol': u'AH.AS', u'Adj_Close': u'13.57496', u'Volume': u'2706600', u'Low': u'13.70', u'Date': u'2014-04-29', u'Close': u'13.92', u'Open': u'13.70'}, {u'High': u'13.735', u'Symbol': u'AH.AS', u'Adj_Close': u'13.34091', u'Volume': u'2784700', u'Low': u'13.56', u'Date': u'2014-04-28', u'Close': u'13.68', u'Open': u'13.565'}, {u'High': u'13.675', u'Symbol': u'AH.AS', u'Adj_Close': u'13.23364', u'Volume': u'3012500', u'Low': u'13.44', u'Date': u'2014-04-25', u'Close': u'13.57', u'Open': u'13.545'}]

--------------------  snip  -------------------

Where I need help it so extract the keys and values and give them the correct model field names in order to save them.

It should be easy really, bt several hours of readin, searching, trial & error haven't given me the trick yet....

Any suggestions?

Thanks!

Oscar

[James Schneider]

On Mon, Jun 8, 2015 at 1:09 PM, Oscar Buijten <oscar....@gmail.com> wrote:
>
> Hi there,
> The python learning curve seems to be steeper than expected :-(
> As yesterday, help converting my php app into python/django will be appreciated.
>
> I have the following:
>
> -------------------- snip -------------------

What does your MyichiHistoricalData model look like? That will be the
primary driver on how you extract the values into the right model
attributes.

If your (required) model fields exactly match the ones provided by
your JSON data, creating/updating the models is probably something as
simple as what is shown in this SO post:

http://stackoverflow.com/a/11487626

If this is a command that will update existing records in addition to
creating them (if this command runs at regular intervals), you may
also want to use update_or_create as opposed to the .update()
mentioned in the SO post.

https://docs.djangoproject.com/en/dev/ref/models/querysets/#update-or-create

Or if you are always going to create new objects based on the incoming
data, look into using bulk_create:

https://docs.djangoproject.com/en/dev/ref/models/querysets/#bulk-create

That will save queries and processing time, but be sure to read the caveats.

> I would need to get 'data' into the 'MyichiHistoricalData' model
>
> The output is like this;
> -------------------- snip -------------------
> [{u'High': u'6.565', u'Symbol': u'AGN.AS', u'Adj_Close': u'6.31564', u'Volume': u'4376000', u'Low': u'6.428', u'Date': u'2014-04-29', u'Close': u'6.551', u'Open': u'6.435'}, {u'High': u'6.479', u'Symbol': u'AGN.AS', u'Adj_Close': u'6.18742', u'Volume': u'3163200', u'Low': u'6.38', u'Date': u'2014-04-28', u'Close': u'6.418', u'Open': u'6.428'}, {u'High': u'6.535', u'Symbol': u'AGN.AS', u'Adj_Close': u'6.18259', u'Volume': u'4780100', u'Low': u'6.379', u'Date': u'2014-04-25', u'Close': u'6.413', u'Open': u'6.529'}]
> [{u'High': u'13.94', u'Symbol': u'AH.AS', u'Adj_Close': u'13.57496', u'Volume': u'2706600', u'Low': u'13.70', u'Date': u'2014-04-29', u'Close': u'13.92', u'Open': u'13.70'}, {u'High': u'13.735', u'Symbol': u'AH.AS', u'Adj_Close': u'13.34091', u'Volume': u'2784700', u'Low': u'13.56', u'Date': u'2014-04-28', u'Close': u'13.68', u'Open': u'13.565'}, {u'High': u'13.675', u'Symbol': u'AH.AS', u'Adj_Close': u'13.23364', u'Volume': u'3012500', u'Low': u'13.44', u'Date': u'2014-04-25', u'Close': u'13.57', u'Open': u'13.545'}]
> -------------------- snip -------------------
>
> Where I need help it so extract the keys and values and give them the correct model field names in order to save them.
>
> It should be easy really, bt several hours of readin, searching, trial & error haven't given me the trick yet....
>
> Any suggestions?

You may also want to have a look at the Django deserialization docs:
https://docs.djangoproject.com/en/dev/topics/serialization/#deserializing-data
I'm not sure if they'll help since you are working with an external
data source.

Assuming that the 'Symbol' will be used as a natural key:
https://docs.djangoproject.com/en/dev/topics/serialization/#deserialization-of-natural-keys


Another note: If you haven't already and before you go any further, be
sure to check with your legal team and with Yahoo!'s terms and
conditions on the use of Yahoo!'s finance data for your own
application. You may not be permitted to use their data in this manner
without some sort of agreement with Yahoo! and/or it's subsidiaries,
even though it is "freely" available through an API.

-James
(Not a lawyer)

[Oscar Buijten]

Hi James,

Thanks for such a detailed response. I looked at all your links (and already had come across some of them)

I guess that my question wasn't specific enough :-(

The table (and model) are existing.

However, the keys that I retrieve in the output I shared do not correspond to (some of) the table fields.

So I am struggling to figure out how to push the values provided by key names that do not match the fieldnames into my table.

As an example;

the key Symbol needs to put it's value into field 'yahoo_symbol' in the model/table

If that would become really complicated I guess I could change the field names in the model/table, but I rather not as the trick in itself would help me with some other data as well (it's part of the learning curve I guess :-) ).

Thanks!

Oscar

[Oscar Buijten]

Oh, here's a part of the model that I forgot to include.

class MyichiHistoricalData(models.Model):

    id = models.IntegerField(db_column='ID', primary_key=True)  # Field name made lowercase.

    symbol = models.CharField(max_length=10, blank=True, null=True)

    exchange = models.CharField(max_length=10, blank=True, null=True)

    yahoo_symbol = models.CharField(max_length=15)

    symbol_period = models.IntegerField()

    timeframe = models.CharField(max_length=10, blank=True, null=True)

    date = models.DateField(blank=True, null=True)

    updated = models.DateTimeField()

    open = models.DecimalField(max_digits=10, decimal_places=4, blank=True, null=True)

    high = models.DecimalField(max_digits=10, decimal_places=4, blank=True, null=True)

    low = models.DecimalField(max_digits=10, decimal_places=4, blank=True, null=True)

    close = models.DecimalField(max_digits=10, decimal_places=4, blank=True, null=True)

    volume = models.IntegerField(blank=True, null=True)

    average_volume = models.IntegerField(blank=True, null=True)

Le lundi 8 juin 2015 22:09:05 UTC+2, Oscar Buijten a écrit :