Again thank you so much! I truly appreciate your direction!
The idea of a class is something I'd unsuccessfully tried but I'm up
for the challenge! I'm very comfortable in Transact SQL but OOP is
new to me. Here's my updated code with a sample XML. The full XML
file is over 600K rows.
I'd appreciate any feedback.
Daryl
import xml.etree.ElementTree as ET
import pyodbc
import dateutil.relativedelta as rd
import dateutil.parser as pr
from dateutil.rrule import rrule, MONTHLY
tree = ET.parse(r'\\DealData.xml')
root = tree.getroot()
class BillDate:
def __init__(self, begin, end):
self.current = begin
self.end = end
def __iter__(self):
return self
def __next__(self):
if self.current < self.end:
billmo = self.current
self.current += rd.relativedelta(months=1)
return billmo
else:
raise StopIteration
for deal in root.findall("Deals"):
for dl in deal.findall("Deal"):
dealid = dl.get("DealID")
for dts in dl.findall("DealTerms/DealTerm"):
dtid = dts.get("ID")
dstart = pr.parse(dts.find("CommencementDate").text)
dterm = int(dts.find("LeaseTerm").text)
darea = dts.find("RentableArea").text
for brrent in dts.findall("BaseRents/BaseRent"):
brid = brrent.get("ID")
begmo = int(brrent.find("BeginIn").text)
if brrent.find("Duration").text is not None:
duration = int(brrent.find("Duration").text)
else:
duration = 0
brentamt = brrent.find("Rent").text
brper = brrent.find("Period").text
perst = dstart + rd.relativedelta(months=begmo-1)
perend = perst + rd.relativedelta(months=duration-1)
months = [dt.strftime("%Y-%m-%d") for dt in rrule(MONTHLY,
dtstart=perst, until=perend)]
for billdt in BillDate(perst, perend):
billdt
if dealid == "706880":
print(dealid, dtid, brid, begmo, dstart, dterm, darea,
brentamt, brper, duration, perst, \
perend, billdt)
<?xml version="1.0" encoding="UTF-8"?>
<DealExport>
<jobid>c03528f241fdc8e4</jobid>
<Deals>
<Deal DealID="706880">
<Properties>
<Property ID="69320">
<Name>12345_Place - Bldg 1</Name>
<CreatedAt>2015-10-28T04:09:07Z</CreatedAt>
<UpdatedAt>2018-04-24T21:56:25Z</UpdatedAt>
<Symbols>
<Type>SourceID</Type>
<Value>12345~12345</Value>
</Symbols>
<Address>
<City>Calgary</City>
<State>AB</State>
<Street>1234 5 Ave S.W.</Street>
<PostalCode>T3P 0N7</PostalCode>
<Country>Canada</Country>
</Address>
<Spaces>
<Space ID="848294">
<Suite>800</Suite>
<SpaceAvailable>11225</SpaceAvailable>
<FloorName>8</FloorName>
<FloorPosition>7</FloorPosition>
<FloorRentableArea/>
<CreatedAt>2016-04-20T13:47:13Z</CreatedAt>
<UpdatedAt>2017-10-02T09:17:20Z</UpdatedAt>
<Symbols>
<Type>SourceID</Type>
<Value>800</Value>
</Symbols>
</Space>
</Spaces>
</Property>
</Properties>
<DealType>new</DealType>
<LastModified>2017-12-20T22:36:21Z</LastModified>
<CreatedDate>2017-06-06T15:09:35Z</CreatedDate>
<Stage>dead_deal</Stage>
<Probability>0</Probability>
<CompetitiveSet/>
<MoveInDate>2017-12-01</MoveInDate>
<RequirementType>Office</RequirementType>
<Tenant>
<Name>Tenant Name</Name>
<Industry>financial services</Industry>
<Affiliation/>
<TenantID>1013325</TenantID>
<TenantWebsite/>
<TenantCurrentLocation/>
<TenantRSFFrom>6000</TenantRSFFrom>
<TenantRSFTo>7000</TenantRSFTo>
</Tenant>
<Brokers></Brokers>
<TenantContacts></TenantContacts>
<DealStages>
<DealStage ID="1533265">
<Stage>proposal</Stage>
<CreatedDate>2017-06-06T15:09:35Z</CreatedDate>
<LastModified>2017-06-06T15:09:22Z</LastModified>
<StartDate>2017-06-06T15:09:22Z</StartDate>
<EndDate>2017-12-20</EndDate>
<DurationInDays>197</DurationInDays>
<DeadDealReasons></DeadDealReasons>
<User>
<UserId>17699</UserId>
<FirstName>Jessica</FirstName>
<LastName>Rabbit</LastName>
<Email>jessica.rab...@themovie.com</Email>
<Phone></Phone>
<PhoneExtension></PhoneExtension>
<Title></Title>
<BrokerLicenseNumber></BrokerLicenseNumber>
</User>
</DealStage>
<DealStage ID="2174388">
<Stage>dead_deal</Stage>
<CreatedDate>2017-12-20T22:36:01Z</CreatedDate>
<LastModified>2017-12-20T22:36:01Z</LastModified>
<StartDate>2017-12-20T22:36:01Z</StartDate>
<EndDate>2017-12-20</EndDate>
<DurationInDays>0</DurationInDays>
<DeadDealReasons></DeadDealReasons>
<User>
<UserId>24934</UserId>
<FirstName>Leigh</FirstName>
<LastName>Vaughan</LastName>
<Email>leigh.vaug...@emails.com</Email>
<Phone></Phone>
<PhoneExtension></PhoneExtension>
<Title></Title>
<BrokerLicenseNumber></BrokerLicenseNumber>
</User>
</DealStage>
<DealStage ID="2174390">
<Stage>dead_deal</Stage>
<CreatedDate>2017-12-20T22:36:21Z</CreatedDate>
<LastModified>2017-12-20T22:36:21Z</LastModified>
<StartDate>2017-12-20T22:36:21Z</StartDate>
<DurationInDays>169</DurationInDays>
<DeadDealReasons>
<DeadDealReason>price</DeadDealReason>
</DeadDealReasons>
<User>
<UserId>24934</UserId>
<FirstName>Leigh</FirstName>
<LastName>Vaughan</LastName>
<Email>leigh.vaug...@emails.com</Email>
<Phone></Phone>
<PhoneExtension></PhoneExtension>
<Title></Title>
<BrokerLicenseNumber></BrokerLicenseNumber>
</User>
</DealStage>
</DealStages>
<DealComments></DealComments>
<DealTerms>
<DealTerm ID="4278580">
<ProposalType>landlord</ProposalType>
<DiscountRate>0.08</DiscountRate>
<RentableArea>6200</RentableArea>
<LeaseType>triple net</LeaseType>
<LeaseTerm>60</LeaseTerm>
<CommencementDate>2018-01-01</CommencementDate>
<TenantImprovements></TenantImprovements>
<BuildingImprovements></BuildingImprovements>
<FreeRents></FreeRents>
<CreatedDate>2017-06-06T15:16:23Z</CreatedDate>
<SecurityDeposit/>
<NER>20714.1144670763</NER>
<NEROverride/>
<DateEntered>2017-06-06</DateEntered>
<OpEx ID="">
<BaseOpEx/>
<YearType/>
<LeaseType/>
<Description/>
</OpEx>
<RealEstateTaxes ID="33598712">
<BaseRealEstateTaxes>7.45</BaseRealEstateTaxes>
<YearType/>
<LeaseType/>
<Description/>
</RealEstateTaxes>
<NPVperSqFt>103367.15936951</NPVperSqFt>
<TotalNPV>640876388.09096</TotalNPV>
<MiscDescription/>
<NetCashFlow>642137720.0</NetCashFlow>
<TotalIncome>502200.0</TotalIncome>
<Concessions>64480.0</Concessions>
<Payback>1</Payback>
<IRR/>
<Latest>true</Latest>
<BaseRents>
<BaseRent ID="45937180">
<BeginIn>1</BeginIn>
<Rent>15.0</Rent>
<Period>rsf/year</Period>
<Duration>36</Duration>
</BaseRent>
<BaseRent ID="45937181">
<BeginIn>37</BeginIn>
<Rent>18.0</Rent>
<Period>rsf/year</Period>
<Duration>24</Duration>
</BaseRent>
</BaseRents>
<RentEscalations></RentEscalations>
<OtherCredits></OtherCredits>
<Commissions>
<Commission ID="260593">
<RepType>landlord</RepType>
<Firm>Company1</Firm>
<Amount>2.9</Amount>
<Unit>rsf</Unit>
</Commission>
<Commission ID="260594">
<RepType>landlord</RepType>
<Firm>Company2</Firm>
<Amount>7.5</Amount>
<Unit>rsf</Unit>
</Commission>
</Commissions>
<Rights></Rights>
<ProposalID>186607</ProposalID>
<ProposalEnteredDate>2017-06-06</ProposalEnteredDate>
<RecoveryIncomes>
<RecoveryIncome ID="33598711">
<RecoveryType>ReimbursableExpenseCam</RecoveryType>
<ExpenseAmount>12.87</ExpenseAmount>
<RecoveryMethod>net</RecoveryMethod>
<RecoveryAmount>12.87</RecoveryAmount>
<Description/>
</RecoveryIncome>
<RecoveryIncome ID="33598712">
<RecoveryType>RealEstateTax</RecoveryType>
<ExpenseAmount>7.45</ExpenseAmount>
<RecoveryMethod>net</RecoveryMethod>
<RecoveryAmount>7.45</RecoveryAmount>
<Description/>
</RecoveryIncome>
</RecoveryIncomes>
</DealTerm>
</DealTerms>
<Budgets></Budgets>
<Appraisals></Appraisals>
<Tours></Tours>
</Deal>
</Deals>
</DealExport>
On Tue, Jul 3, 2018 at 3:42 AM Alan Gauld <alan.ga...@yahoo.co.uk> wrote:
>
> On 02/07/18 23:52, Daryl Heppner wrote:
> > The two While Loops are the part I'm struggling with. The goal is to
> > list each bill month number and date for each month in the term.
> When you need multiple values you need to use a collection
> type such as a list or tuple. Your problem is that you are using
> single valued variables to collect data but as you go round the
> loops (not just the while loops) you are throwing away the data
> you have already collected so that when the loop ends you only
> have the last set of values.
>
>
> > this example, I'm trying to return 36 rows, listed from 1-36 instead
> > of one row followed by 24 rows numbered 37 to 60.
>
> Personally I'd create a dictionary per row and store those
> dictionaries in a list. (You could use a class instead of a dictionary
> but I'm guessing that might be too big a step for a beginner.)
>
> I'd also put all the parsing code into a function so that you
> pass it the raw xml and get back the row dictionary.
>
> That should simplify the structure of your code significantly
> and make it easier to see what's going on. It should also be
> easier to test the function on its own with some sample xml.
>
> If you need more help with that let us know.
>
> --
> Alan G
> Author of the Learn to Program web site
> http://www.alan-g.me.uk/
> http://www.amazon.com/author/alan_gauld
> Follow my photo-blog on Flickr at:
> http://www.flickr.com/photos/alangauldphotos
>
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
