Add portfolio and alerts via email

Anything is possible in theory.

The above change you suggest depends on something I have in the wish list, which is to allow the user to store a real portfolio, but have not implemented it yet.

By real portfolio, I mean that we have the stock table that has the following fields: User ID, symbol, Quantity (i.e. number of shares), Cost (what they paid per share), hi limit, low limit

Then there would be multiple rows for each user, e.g.

User 2 will have RHAT and IBM with cost and limits.
User 220 will have LU and HPQ with cost and limits.

You get the idea.

Then, it would be very easy to all sort of magic, such as:

Send alerts:

For example, send a daily alert by email if any stock is below a certain percentage. Or send an alert when total portfolio is below a certain threshold.

Portfolio:
Give the total value of investment, and profit or loss in dollar value and in percentage

As well as other things.

This can evolve into finance.yahoo.com, isn't it?

The easy part (for me) is writing the alerts, portfolio screens, ...etc. The part I do not like to do is writing the user interface (all these fields on a screen, with add, change, delete capabilities, ...etc).

If someone is willing to do the user interface part, I can do the rest. Even if someone can write a template of add, change, delete, I will look into expanding that.

If I've understood you know how make the site do the automatic call of new price and do check and do the email too (this is for me the objective)

ok finance.yahoo si the top, but it's too much (for now :)
remember that to control a real portfolio you must play with exchangerate other than price too!

if you plain to try something with alert on the price for start
(correct the idea of one row for each user and ticker)
I can try to explore the world of the php studiyng how to set a page like

ticker fixed
last price fixed
control < or > editable
limit price editable

nota : I'm a newbie oh php but as you say all is possible
can you guide me to some example that I can analyse?

Yes, I know how to send emails for certain events, and how to add a cron hook so every 5 minutes (or 2 hours or whatever), the values will be calculated and an alert sent.

For exchange rate, it would be too much, as you say. The assumption is that you are buying stocks in your one currency (e.g. all US stocks in US dollars, Japanese stocks in Yen, ...etc.)

We can even take it a step further and add a portfolio_id column, so we allow a one to many relationship between a user and portfolios, and they can have several portfolios, each with its own currency (e.g. US stocks as portfolio1, day trading for 2, ....etc.) then we have a table called portfolios which stores a description and currency, and user id.

user 1-to-many portfolio 1-to-many stocks

portfolio will have: pid (portfolio id), uid (user id), description, currency (3 letter international symbol for example).

stocks will have pid, ticker, price_paid, hi_limit, low_limit.

The price of each stock will be gotten dynamically (from Yahoo Finance, like the module does today).

The part that will take a lot of time to implement (for me at least) is the user interface part: the user would have a screen to "add a stock", then display input fields for stock ticker, price you paid for it, and the limits you want (hi/low). All are editable. Then the option to add another row (new symbol), then another, ...etc.
Also, the option to delete a symbol, or modify the data of an existing symbol. That part is not particulary hard, but it is tedious, and would ideally be done by someone who knows UI design (user interface).

Something like what you find in http://example.com/admin/user would still work for sure (an 'add' tab on the top, an edit and delete links at the end of the line), but for a large number of stocks it takes a long time to mass-edit or mass-delete them. Ideally it would display all the portfolio with editable fields in one screen so as to avoid the multiple clicks required.

using you assumption on the currency and using the idea of multiple portfolios you have described the working of my pocketpc program (now blocked by proxy and firewall): good !

user 1-to-many portfolio 1-to-many stocks

ok I want try to do that for start and I'm studing the admin/user interface to replicate it to manage the following tables
#
# Struttura della tabella `portfolios`
#
# Creazione: 12 Feb, 2005 at 07:10 PM
# Ultimo cambiamento: 12 Feb, 2005 at 07:10 PM
#
CREATE TABLE `portfolios` (
`pid` int(11) NOT NULL default '0',
`uid` int(11) NOT NULL default '0',
`description` varchar(64) NOT NULL default '',
`currency` char(3) NOT NULL default ''
) TYPE=MyISAM COMMENT='4 stock.module';
#
# Dump dei dati per la tabella `portfolios`
#
# --------------------------------------------------------
#
# Struttura della tabella `stocks`
#
# Creazione: 12 Feb, 2005 at 07:18 PM
# Ultimo cambiamento: 12 Feb, 2005 at 07:18 PM
#
CREATE TABLE `stocks` (
`pid` int(11) NOT NULL default '0',
`ticker` varchar(128) NOT NULL default '',
`price_paid` float NOT NULL default '0',
`hi_limit` float NOT NULL default '0',
`lo_limit` float NOT NULL default '0'
) TYPE=MyISAM COMMENT='4 stock.module';
#
# Dump dei dati per la tabella `stocks`
#

btw: currency = 3 letter ISO symbol ok

with these name of tables I can try without danger (I suppose) for the actual stock.module
For now is my best of possibility, but the screen that display all the portfolio with editable fields maybe one day
ell me everything that you think I need to reconsider

stay tuned
ciao

I did some hacking yesterday eveing, and reached almost the same structure you proposed.

Here it is:

--
-- Table structure for module 'stock'
--

CREATE TABLE stock_portfolio (
uid int(10) NOT NULL default '0',
pid int(10) NOT NULL default '0',
name varchar(128) NOT NULL default '',
currency char(3) NOT NULL default '',
alert_high decimal(15,5) NOT NULL default '0.00',
alert_low decimal(15,5) NOT NULL default '0.00',
PRIMARY KEY (uid)
) TYPE=MyISAM;

CREATE TABLE stock_stock (
pid int(10) NOT NULL default '0',
ticker varchar(10) NOT NULL default '',
quantity decimal(15,5) NOT NULL default '0',
price_paid decimal(15,5) NOT NULL default '0.00',
alert_low decimal(15,5) NOT NULL default '0.00',
alert_high decimal(15,5) NOT NULL default '0.00',
PRIMARY KEY (pid)
) TYPE=MyISAM;

The only real difference is using decimal instead of float, and using stock_prefix for tables. The later is not necessary, but use of singular is preferred (stock instead of stocks, porfolio instead of portfolios).

If you can create the screens that populate these two tables, I can write the rest (display of portfolio details per stock, e.g. gain/loss, total value, ...etc., and email alerts, cron interface, ...etc.)

ok
I'll try to create editing page for your table stock_name convension

(btw why alarm on portfolio? for this you need quantity in the stock_stock too...
maybe we can add....)

ciao

sorry
only now i've noticed that you already have put quantity in the stock

so i can write to you something that i've put only in my memo ...

and after maybe complicate everything with exchangerate and quantity and date (in ISO YYYYMMDD):
I can buy apple aapl.us and pay it in EUR (base currency of the portfolio) but when I'll ask the price I'll get 78.xx USD (I imagine depending of the part after the point in ticker.XY and the module need to convert it in EUR,
so I need download the exchangerate from the asset in some way e apply this the the value if I want to know the value in EUR
and if I've the Quantity I can valorize my portfolio
and if I've Paid_Price and Buy_ Date I can do basicly performance calc
and dream so on ....

The alert on the portfolio allows the investor to say "tell me if my investment fall below 10,000$" or "tell me if my investment is more than 12,000$".

This allows them to sell before losses are too much, or adjust the portfolio if it makes more than a certain amount (sell the gains and buy other stocks, retire to a tropical island, marry a younger wife, ....etc. ;-) )

For exchange rate issue, let us keep it simple: you pay EUR, or camels or any currency you want. Then you calculate how much EUR you paid for AAPL.US and enter the USD equivalent in the portfolio.

The portfolio will get the stock price and calculate everything in one currency.

You can set every portfolio to be in a different currency for that purpose. For example, one for Paris Stock Exchange (FFR), one for FTSE (GBP), one for NASDAQ (USD), ...etc.

What do you think of that?

I agree for all, you have more experience
(KISS = keep it simple stupid is the best way!)
( but don't buy nothing in FFR because is not valid anymore :)

success
I can see a menu in my test file portfolio.module :)
..still working...

btw you need to modify the unique key on stock_stock
I'm testing with unique key based on pid AND ticker

probably also stock_portfolio need a different unique key
uid and pid ?

Hi Roberto.

Is this ready for testing yet? I mean, when you have something ready, I can take it and build the rest (insert/update/delete to the database, portfolio page and block, and email notify on thresholds)

hi
I've a version that can do ADD portfolio and stock

tomorrow, or a little after, I hope to work to the edit part and the thing will be ready for you

btw: but the cron part will work on windows site too?

The cron part of Drupal works on both Windows and UNIX/Linux.

The module called poormanscron can be used on any platform when there is no cron functionality externally to Drupal.

this is the version with ADD functionality
(i need to expand the add stock part and find the time to work to an edit part but it start to be usable)

If you want to give it a try you can put it in the same dir of stock.module and load the module, actually it's called portfolio.module but it's in very beta version

open to every suggestion
bye bye

Roberto!

This looks like a REALLY great start with the tabs and everything.

I will have to take it for a test drive on the weekend.

One comment: instead of entering the PID on the add stock tab, there should be a drop down list of existing portfolios, and the user selects from them. This will save us validation checking (portfolio does not exist, ...etc.).

If you have a newer version by end of Friday, or mid day Saturday (Italy time), then attach it here, and I will use it.

Thanks for the good work. This will work to be a great module.

Hi Khalid,
ok this is my last version

I'm testing with update query
(but I'm now thinking that will be better: read all the value for the stock, put them on screen, alllow the user do the modification and then delete using the argument passed in the path and reinsert all the stock: what your impression?)

the buy and sell part are for now only in my mind (to recalculate the price_paid if you buy and sell)

for the next days I'll try the experiment in the use of the combo,

can you put the file in cvs in some way and give me the basic instruction to access it?

ciao!

delete using the argument passed in the path

If you mean that we will have http://example.com/stock/delete?s=ibm,lu,rhat then I do not like this. Drupal has the advantage of using clean urls, with minimal & or ?. I don't want that to change. So whatever we do will be done either using clean URLs or variables that are not in the URL as arguments.

the buy and sell part are for now only in my mind (to recalculate the price_paid if you buy and sell)

Let us keep that for a future version. Great idea.

can you put the file in cvs in some way and give me the basic instruction to access it?

I don't want to commit it to the repository, unless it is somewhat working. We do not want people downloading it and them asking us for help. Or perhaps we can create a new project for it (e.g. portfolio) in CVS without a project page? Once I get at least the basics working, I can setup a repository for it.

"If you mean that we will have http://example.com/stock/delete?s=ibm,lu,rhat then I do not like this. "

nor me

I'll try to explane better now that I'm arrived at home:
http://example.com/portfolio/stock/edit/1/ibm or similar bring to the stock edit page

I'm using arg(3) and arg(4) to get the stock
I like to show all the data in the table stock_stock for pid ='1' and ticker ='ibm' on screen allowing the user to do changes and at this point :
case a: update of the stock with update stock_stock set field ='value' etc etc where pid ='1' and ticker ='ibm'
case b: delete of the line where pid ='1' and ticker ='ibm' AND insert again a new line with the data in editing like add stock

actually I was testing case a but i'm now oriented to test case b that maybe it's better

"the buy and sell part "
can be hidden simply removing the callback so I can concentre my study to the combobox part

"I don't want to commit it to the repository, unless it is somewhat working. "
no problem at all I can save in this way and pass you update, but please let me create a new point without all this followup

Roberto, can you please click here and send me a message? http://drupal.org/user/4063/contact

I think it is time to discuss things on IM.

Roberto

Two comments:

1. Regarding the buy and sell, it is an overkill to do. The user can do the calculation outside of Drupal, and enter his cost per ticker and quantity. Even Yahoo does it this way. We will not maintain transactions and history. At least not for now.

2. The patches you attack are in HTML with colors ...etc. Please make them plain text in the future.

a question outside the project:
If I shold open an account for american transactions
which can you me suggest:
Ameritrade, E*trade,....
ciao

I am closing this feature request, as it's for a not supported Drupal version. If you want to see this feature implemented for a supported project version, please re-open this issue.