9 Replies Latest reply on Mar 13, 2012 10:03 AM by philmodjunk

    Calculation question

    citruspips

      Title

      Calculation question

      Post

      Hallo I hope someone can help with my, probably, stupid question.  

      I'm new to Filemaker and trying to make a database to replace my iNumbers spread sheet, which track the performance of a basket of stocks in an investment portfolio.

      I have managed to create a table which records cash, dividends, fees, buys sells etc to provide an overall profit/loss. However to evaluate overall performance I need to add the current daily value of the stocks held (this value is constantly changing as per stock market valuations)

      In iNumbers I did this by having a second table which imported totals from the first and I was able to then insert the current days valuation in one cell to evaluate the overall performance at that time.

      When I try this in Filemaker I cannot get it to work. I cannot seem to carry out a calculation between tables despite trying all sorts of 'relationships'. 

      To illustrate:

      I have £2000 in cash.

      I buy stock 'Z' for £1500

      Therefore I now have a cash total of £500 + the market value of the stock "z"

      but today my stock may be worth £1200 and next week it may be worth £1600 its this dynamic changing value I want to include but cannot figure out how.

      I no doubt have missed something obvious but I'm totally stuck.

      Thanks

        • 1. Re: Calculation question
          philmodjunk

          What did you try to get it to work that didn't?

          Why do you need a related table instead of just a field for the current stock value? Do you want to keep a separate record of each day's stock value?

          Unless you want to do that, I don't see the need for a related table.

          In your original table, what does one record represent? One Stock transaction? One stock? or ??? (I see you are tracking  a cash balance as well as the purchase of a stock, but knowing the details helps suggest a relationship that will work for you if you need such a related table.)

          • 2. Re: Calculation question
            citruspips

            Thanks for responding.

            Heres A simplified version of my table

            Table Name...Transactions

            Fields............Date

                                Ticker ( identifies the stock and I've managed to create a portal to see company info for given stock)

                                Cash (Nb  if i take cash out I use a negative number although in reality this field is more or lesss a constant)

                                Buy  

                                Sell

                                Dividends

                                Fees

                                Cash balance (calculation:(cash+sell+divi)-(buy+fees)

             

            To measure current performance at a given time I need to know the overall valuation of my portfolio. So I wanted to add two further fields:

                               Current Market Value (this value is always changing)

                               Valuation of Portfolio  (calculation:Cash balance+Current Market Value)

            My problem is where to put Current Market Valuation. I had tried a seperate table because if I entered the value as £2000 today and then tomorrow I enter it as £2200 my database adds them together rather than just using the current valuation. I had thought If I used a seperate table I could simply replace the existing value with the current one but I cannot get the calculation to work across the two tables.

            At this stage I don't need to track performance over time (too complicated). What I'm trying to acheive is a simple form which if i enter todays market valuation would generate a figure that reflects the performance of my portfolio in relation to the amount of cash invested.

             

             

             

             

             

             

                                

            • 3. Re: Calculation question
              philmodjunk

              If you buy 200 shares of Stock X last week and purchase another 500 shares of stock x this week, do you have 2 records for stock x or one?

              • 4. Re: Calculation question
                citruspips

                Hallo

                I would have two records. 

                • 5. Re: Calculation question
                  philmodjunk

                  And both need to access the same related record that reports the current stock value.

                  Define this relationship between your two tables:

                  Transactions::Ticker = StockValues::Ticker

                  Then calculation fields defined in Transactions can refer to StockValues::Value to get the current value of a single share of stock.

                  • 6. Re: Calculation question
                    citruspips

                    Thank you for helping. I have tried this but I cannot get it to work with the totals. The variable number is a total value of my current stock holdings. I must be doing something fundamentally wrong.

                    To illustrate 

                    If table one has Two fields: Key (a serial number)

                                                        Numbers ( just a list of numbers)

                     

                    How could I add up the numbers field then add this total to a number that each day may change to assertain a current total. Its the total which I worked with in my spread sheet but I can't seem to do this here. 

                    • 7. Re: Calculation question
                      philmodjunk

                      I don't understand the structure of your data here. What do you mean by "a list of numbers"?

                      If the Transactions table records the number of shares purchased and "ticker" stores the unique stock symbol for that stock, then each Transactions record can use a calculation field, cTransactionValue, to compute the current value (for that transaction) as:

                      NumberOfShares * StockValues::Value

                      Then you can define a summary field as the Total of cTransactionValue

                      To see the total value of any given stock, either make this field a running total, (restart totals with each group) and use Ticker as the "group by field" or add a sub summary part "when sorted by" Ticker and put this summary field in the sub summary part.

                      With either option, you must sort the records by Ticker to group stock transactions by stock to see a sub total for each type of stock purchased.

                      • 8. Re: Calculation question
                        citruspips

                        I suppose what I'm trying to do is include a  'comparison' between the total purchase cost ( as worked out in the way you describe above) and todays stock value (taken from the total on my stockbrokers live statement). 

                         

                        I could of course just look at my stockbrokers valuation but it doesn't take into acoount any other factors eg cash, fees, dividends etc it just measures the performance of a portfolio in the context of current stock owned. 

                         

                        I get the feeling it would be more logical to deal with the individual stocks rather than the aggregate value of the portfolio. So I will have a go in that direction.

                         

                        Many Thanks

                        • 9. Re: Calculation question
                          philmodjunk

                          What I describe makes both calculations possible: the current value of the stocks purchased/sold in a given transaction record and the total of all such records--for either a specific stock or all stocks combined.