• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Conditions to solve a one to many, many to one problem

E

eBoof

New Member
Firstly thanks to Chandoo for a wonderful sight. It's the first time I've really been able to get the hang of writing somewhat pseudocode before I start and it was a great help. I've included it here in case the section on this problem is helpful.


The Spreadsheet is record of shares bought and sold on the stock market.

data looks like this

Date, ASX Code, Contract No, Units, Price, Trans Type (whether bought or sold)

N.B. Contract No is unique

ASX Code is a three or more letter code that identifies the company shares being bought or sold e.g. AAPL

N.B. I never short sell so I must own sufficient shares before I can sell them. (this makes me think that a condition based on date prior to sale may be helpful)


A separate sheet is created for each individual stock e.g AAPL, MSOFT etc.

It records all transactions for that stock and a separate table for the record of sales.

There latter table tells me what share I still hold, how many on each contract and the cost of those remaining shares.

It also tells me how much profit I make on each sale.


Sample data not used in attached spreadsheet but to explain this as simply as I can.

1/1/12, BHP ConNote A001buy 100 shares totalling $1200

2/1/12, BHP ConNote A002 buy 100 shares totalling $1150


Now the problem is when I sell.

I could sell 50 shares from either parcel and end up with RemNumShares = 50 for one ConNote and 100 for the other

I could sell 100 shares from either parcel and end up with RemNumShares = 0 for one ConNote and 100 for the other or,

I could sell 150 shares from either parcel and end up with RemNumShares = 50 for one ConNote and Nil for the other or of course sell the lot and that makes it easy but just another condition.


This scenario affects the number remaining the cost of those remaining and the profit on the sale.


Hold it, don't get too excited because there's one extra problem. I can't do it on FIFO. I need to be able to select which contract I want to first apply the sale to. This is because here down under there can be a tax benefit for doing so.


I really hope I can make some progress with this I've been trying to do it for a number of years in Filemaker and I just can't get the relationships right.


Here's the link to the excel file

https://www.dropbox.com/s/0dygfy4hnro1irf/Sample_Share_Portfolio_File.xls


and to my Pseudocode. If anyone is interested I'd appreciate some tips on the way I've done it.

https://www.dropbox.com/s/nidjklu48i2jh03/Share%20Project.pdf


Thanks for your help

Rob
 
Hi Rob ,


Quite an interesting project. Here's wishing you a successful completion.


Can you clarify why you wish to store transaction data stock code wise , with a separate tab for each stock ?


Are you going to have a userform for data entry , editing / deletion , and display / printing ?


As far as I can see , the outcomes are reports / statements , which can be done more professionally using VBA without the need for intermediate worksheets ; having the data in just one place will make it easier to audit and secure. Having multiple worksheets containing the same data in different forms is not really good IT practice. Generating the reports "on the fly" will ensure that your outcomes are always up-to-date.


Narayan
 
Hi Narayan,

I hadn't really considered doing it like that in Excel.


That's pretty much how I was doing it in Filemaker. I have no experience with Excel with macros or userforms. I'm using a Mac Excel 2011 and the only other experience was with Applescript again something I needed to learn as I went along.


My aim was to just enter the data in once and then create the spreadsheets from there. It would have been a big project and I just have no idea how to do it the other way.


Is there a sample you can post for me to have a look. The stumbling block with Filemaker and doing the spreadsheets has always been the treatment of the three different ways the sales Contracts are handled.


BTW, the spreadsheet for each one is something that evolved from my spreadsheets over the years. I'm completely open to your suggestion, I'd just like to know where to start and how to tackle the Sale Contract Note dilemma.


Thanks for your interest and help.

Cheers

Rob
 
Hi Rob ,


I thought since you have drafted the functional specs in pseudocode that you would eventually develop the whole application using VBA. Unfortunately I am using Excel under Windows and I have no idea about the differences between Excel for Windows and Excel for the Mac. Probably others can chip in.


I googled about data entry in Excel , and some good links are :


1. http://contextures.com/xlForm02.html


2. http://www.contextures.com/exceldataentryupdateform.html


3. http://www.vtc.com/products/MicrosoftOfficeforMac2011/Excel/107992


Narayan
 
Hi Rob ,


I think people in this forum can help out in whatever you wish to do ; if you can put together a sample worksheet , and upload it , we can start from there. We can take your outcomes one by one , detail the report format , and develop the VBA code to do that ; I think when the first one is done , you'll be better equipped to tackle the rest.


What is needed is just the raw data worksheet with at least 50 transactions.


Narayan
 
Thanks Narayan, I'll get the data together and do some reports in pdf for the outcome I need. Would that be helpful?

Cheers

Rob
 
Hi Rob ,


That will do ; if you can upload your workbook with some sample data in it , and the pdfs of your reports , we can start working on it.


Narayan
 
You must log in or register to reply here.
Back
Top