Ledger1 Forum

See what other Ledger1 users are saying about how they use Ledger1 in their home or business. Search bar at top of page.

Get Started with Ledger1

New users of Ledger1 can come here to find tips and information on how to set up a new Ledger1 file.
 Views
1Posts

How Do I....

Not sure how to use Ledger1 to get the best results with the data you have? Not sure how to best use Custom Fields?
 Views
7Posts

General Excel Questions

Need help with an Excel formula? Have Excel formatting questions? Check here for any type of Excel question.
 Views
0Posts

Accounting / Tax Issues

Not sure how to categorize a Transaction? Wonder how others handle accounting or taxation issues and tasks? Try here.
 Views
0Posts
New Posts
  • Ledger1
    Dec 6, 2018

    If you are using the latest version of Excel, as of November 2018, a new stock price updating method is available. Here is a great article from Excel University on how to use it: https://www.excel-university.com/stock-data-type-stock-quotes-and-more/
  • Ledger1
    Dec 6, 2018

    The Securities Page in Ledger1 uses services provided by EIX Trading to import live stock prices. Price (delayed 15 minutes) is one of many stock statistics that EIX makes available for free. For example, here are some other widely used stats that are easily imported to Ledger1: Opening price (open) High for the day (high) Low for the day (low) Closing price (close) Volume (latestVolume) Average Volume (avgTotalVolume) 52 week high (week52High) 52 week low (week52Low) The formula Ledger1 uses to bring live data into column F on the Securities Page is this: =IF((OR([@[Security Type]]="Stock",[@[Security Type]]="ETF"))=FALSE,0, NUMBERVALUE(WEBSERVICE("https://api.iextrading.com/1.0/stock/" & D8 & "/quote/latestPrice"))) The last term in the formula is "lastestPrice". Just replace this code with any of the codes shown in parentheses above to get the statistic you want and you'll be on your way to creating a powerful trading tool. Many more codes are available at the the following EIX Trading website: https://iextrading.com/developer/docs/#quote
  • Ledger1
    Apr 3, 2018

    UPDATE: A Bank Reconciliation Template is now available on the "Free Downloads" page of this website. Try it out! Ledger1 doesn’t come with a built-in selection of canned reports. We hope to include downloadable sample reports in the future. Currently, the way to create reports is to start with the reports page and use the formulas to design the report you need. Here is one website that provides a large selection of free sample reports: https://www.sampletemplates.com/?s=bank+reconciliation Here is one way to create an automated Bank Reconciliation Statement that would allow you to enter a date and account name and then complete the report by adding reconciling items to the body of the report: 1. Use the “cleared” column on the Ledger page. When you enter a transaction, or import a group of transactions, Ledger1 marks them as “cleared” by default, by entering a “C” in the cleared column. If you remove the “C” for pending items, Ledger1 will display 2 balances in the upper left of the page: an actual “cleared” balance and a balance that includes all pending items. These are the two numbers you are reconciling. You can add these two numbers to any report using the following two formulas: To provide a cleared balance: =SUMIFS(Transaction_Table[Credit],Transaction_Table[Account],C3,Transaction_Table[[Date ]],"<="&D2,Transaction_Table[CL],"C")-SUMIFS(Transaction_Table[Debit],Transaction_Table[Account],C3,Transaction_Table[[Date ]],"<="&D2,Transaction_Table[CL],"C") In this example, the Account Name is assumed to be in cell C3 , and the reconciliation date is assumed to be in cell D2 . To provide a pending balance: =SUMIFS(Transaction_Table[Credit],Transaction_Table[Account],$C$3,Transaction_Table[[Date ]],"<="&$D$2,Transaction_Table[CL],"C")-SUMIFS(Transaction_Table[Debit],Transaction_Table[Account],$C$3,Transaction_Table[[Date ]],"<="&$D$2,Transaction_Table[CL],"C") If you use this method, all you’ll need to do is enter and account name and date in the referenced cells and the report will be updated. Then you can add more lines to the report as needed. If you have a large number of pending items that you need to list on your report, you could filter the Transaction Data Table to show only the pending items, then copy and paste them to your report. The more advanced way would be to create formulas to lookup the pending items, or even use a pivot table in the body of the report.