12 Replies Latest reply on Feb 23, 2011 5:55 AM by LarrySanders

Using a different record in calculation.

Title

Using a different record in calculation.

Post

In my Table, I have Row data defined by Year.

Year       Capital      Income      Return on Capital(which is calculated)

2000       196            90

2001       320            158          (Current Year Income/Previouse Year Capital)

The problem I have is this:

I need to set up a Calculation Field, Return on Capital, that uses current year's income divided by previouse year's Capital. The correct number is 0.806122. But I do not know how to do it with FM Pro 11.

• 1. Re: Using a different record in calculation.

Do you have only one record for 2000, and one for 2001?  or at least can you define a relationship (I dunno... CustomerID ->CustomerID?) which will get the records for that customer (project, asset type..., whatever)?

Create a calculation YearPrevious = Year - 1

Create a relationship from the table to itself that includes the relationship criterion:

CapitalTable::YearPrevious = CapitalTable2::Year  (CapitalTable2 is a second Table Occurance of the table CapitalTable)

Then for any record you can see the return on capital - it is:

Income / Capital <--- where that capital figure is picked up from the relationship you just created

• 2. Re: Using a different record in calculation.

Thank you very kindly for your replay. I am not equiped to understand this yet. I don't know what is Table Occurance and how to populate CapitalTable2. It all sounds very logical, but it is not happening for me.

Perhaps my thinking is on the wrong path.

I have 3 tables, which really should be one. I did not want too many fields in one table because I import all updates from Excel, too many fields are difficult to manage.

All 3 tables are identified by Ticker and Year, which is how I defined the Relationships. The 3 tables always have the same number of records (this makes me feel silly). I then created a seperate table to perform calculations, pulling records from each of the 3 tables.

One of the caluclation requires me to use current year income, and previouse year assets.

I suspect there are easier ways to keep the arrangement clean and simple, but I have no idea how. Thanks for your help.

Sincerely,

• 3. Re: Using a different record in calculation.

I got it!

Thanks a million for the briliant idea.

Please do comment on my 3 table relationships. I am not sure that was the best thing to do.

• 4. Re: Using a different record in calculation.

"I don't know what a Table Occurance is" - Don't worry about learning about Case Statements and the syntax of a MicddleWords funstion now - you can add any of that knowledge any time later with 30 seconds reading of the Help file.  But: you will not be able to (easily) change your file structure of Table Occurances once you realise that the way you started out doesn't accommodate the inevitable expansion that your system will enjoy.  I beg you to read the links in this post, and spend some time understanding it.  It is a bit tricky to get your head round at first, but it is the only tricky thing about Filemaker, and it will quickly become clear once you use it.

In the post Phil says, "There's no right and wrong way", and of course the Anchor & Buoy is only one suggestion of how to organise TOs.  But I would say, "Use it first, and only after you've understood it should you recommend changing to another system."

http://forums.filemaker.com/posts/1c39ff199a

--------------

"...and how to populate CapitalTable2." - you don't have to.  The data that is in CapitalTable1 will be replicated (or think of it as that) in CapitalTable2.  The tutorials above will explain that.

------------

First thoughts are that you don't need more than one table, escpecially if your reasoning is just to keep down the number of fields, or because you are importing from Excel.  But then we can't pass comment on your table needs until we know what you are trying to do.

We know you have a table of investments, which have the fields:

Year

Capital

Income

...and you may have hundreds of calculations which are derived from that basic data ('Return' being one of them).  What other basic data do you hold in the file system?  Customers' names,for example?

• 5. Re: Using a different record in calculation.

Thanks for the post. I have been thinking about solutions for sometime while learning how to use FM. I must share with you where and how I get data input so I can get around what I do not already know about FileMaker.

I read 10-Ks, the SEC filing of financials by businesses. I read a great many of them. These documents are filed annually (acutally quarterly, but no matter). There are 4 basic statements filed: Income, Balance, CashFlow and Change in Equity.

For my analysis, I need the first three statements. For each company, there are multiple years of data needs analysis, usually more than 10 years.

The way I identify a unique record is by a set of Ticker and Year.

For example, KO and 1999 defines the statements for CocaCola for the year 1999.

So for each Ticker and Year, it defines Income, Balance and CashFlow of a particular business for a given year.

These statement are already downloaded from SEC website in Excel files, but not in a readily importable format. I must select data from each statement and transpose it to the form that can be imported by FileMaker.

Income Statement contains the following fields:

Revenue

CostOfRevenue

GrossProfit

InterestExpense

Tax

NetIncome

Balance Sheet Contains:

CashAndEquivalent

MarketableSecurity

AccountsReceivable

Inventory

PrePaidExpense

OtherCurrentAssets

AccountsPayable

CurrentPortionOfDebt

FixedAssets

EquityMethodInvestments

GoodWillAndIntangiles

AccountsPayable

CurrentPortionOfDebt

DeferredTax

AccuredLiability

LongTermDebt

PreferredStock

CommonStock

PaidInCapital

RetainedEarnings

ComprehensiveIncome

TreasuryShares

Euity

CashFlow Statement contains:

NetIncome

DepreciationAndAmertization

StockCompensation

ChangesInWorkingCapital

CashFromOperations

PurchaseAndSalesOfAssets

CapitalSpending

CashFromInvesting

NetChangInDebt

IssuranceOfStocks

RepurchaseStocks

Divdends

CashFromFinancing

End of Data sets.

As you can see, I have three sets of data all related by the same set of Ticker and Year.

Ideally, they should go into one table. But the way financial statements are published is tricky. We have 3 years for Income and CashFlow statements, and 2 years for Balance for each of the 10-K filing. So when I import data, it is guarenteed that I have to import them seperately, on individual basis. This is the reason I went to three tables. It makes the routin import more managable, and I can see more easily if there are problems.

These things starts out simple, but can get tangled as time goes on. I must keep each step as simple and managable as possible so I can have confidence in ensuing analysis. The integrity of data must be assured at all times.

The calculations involves using records from each of the three tables, and at times from different years. I actually add three additional tables for calculations purposes. All these are in an effort to keep everything transparent, clear and easy to follow at all times.

I feel these questions are a lot to ask. I really appreciate the way your guys share your knowledge.

I do not think my problem is particularly difficult for a database, it is just I have never used one and do not know what I missed. I have hundreds of Excel files, it is very difficult to string the thoughts together that way. If I design a lousy framework and put all the stuff in there, it will be pain to realize I should have done something differently some months down the road.

Thanks again for sharing yoru thoughts.

Regards,

• 6. Re: Using a different record in calculation.

"These statement are already downloaded from SEC website in Excel files,  but not in a readily importable format. I must select data from each  statement and transpose it to the form that can be imported by  FileMaker."  - Can you send me a link to a typical sheet?

"Ideally, they should go into one table" - I agree.  As I was reading down the list I couldn't see any reason jumping out at me why you separate them (or keep them separate).  I'm still reading...

"We have 3 years for Income and CashFlow statements, and 2 years for  Balance for each of the 10-K filing" Does that mean that (as a point of principle):

in year...              you see the Statements for...

2010                    2010, 2009, 2008

2009                    2009, 2008, 2007

2008                    2008, 2007, 2006   where 2008 in this listing is the same as the 2008 supplied in 2009 above, and in the 2010 above that?

"So when I import data, it is guaranteed that I have to import them  separately," - no; still don't see why.  I'm not saying it's easy, but then you're managing to do it ok now, so you've figured it out.  And my reasoning is that if it's tricky to do when you (the human) are doing it, then that's all the more reason to check it over once, make sure it's all correct, then get Filemaker to do all the importing automatically.  If that needs to be 25 carefully-thought out and sequenced steps, then my view is, "Thank goodness Filemaker scripts do this."

The most awkward thing to accommodate would be if every year's report was in a different format.  But I'd be surprised at that; the SEC will have lots of people downstream of their reports who would be most upset if they kept changing the order or number of columns.

I'll reserve judgement until I see a typical Excel sheet!

• 7. Re: Using a different record in calculation.

The best way to do this is perhaps by walking through the process one time. Lets look at two similar businesses, the easiest of all cases. The goal is to get both CocaCola and Pepsi financial statements, as filed with SEC, into the same data table.

CocaCola first

In the search pull down manual, select Annual Filing.

You will see 10-k filings in Excel and PDF. The simpler way is to use PDF to extract only the statements you are interested in. Excel file will give you all numerical tabulations. If you are not familar with the filing process, it can be confusing.

Assume you get to Income, Balance and CashFlow statements, you will see that Income and CashFlow are stated for 3 years, and Balance for 2.

So far we have not problems.

Now if you goto http://www.pepsico.com/Investors/SEC-Filings.html and do the same things, you will get Pepsi's statements.

You will see that although the two businesses are generally similar, and both file the same statements, how they arrange information and what to call each of the items can be slightly different. This is because GAAP (Generally Accepted Accounting Principle) is a guideline, not a script to be followed.

I would like to send you the Excel files, but I couldn't figure out how to attach them. If you can suggest a way to get the files over to you, it will be easy for you to see what I am working with.

Give the links above a try, if it is too much trouble, lets figure out an easier way to get the files to you.

I have a feeling that there are better ways to do what I am doing. Appreciate your thoughts.

Regards,

• 8. Re: Using a different record in calculation.

"how they arrange information and what to call each of the items can be  slightly different" - that will be the killer to automation..

I'll have a look later.

• 9. Re: Using a different record in calculation.

We can get pass that difficulty. I go through hundreds of files to standardize the catogories. Basically, I creat a standard Excel format file for each of the 3 statements, then import each into its own FM table.

• 10. Re: Using a different record in calculation.

I have attached a sample Excel file and sent it to you.

Over time, all I need to do is to add additional data as new filings become available.

Thanks for the help.

Regards,

• 11. Re: Using a different record in calculation.

I've sent you a file that imports the data from the excel sheet.

• 12. Re: Using a different record in calculation.

A replay is sent to address the number of records and why I do not want to enter data mannually.